VBA to relocate XML blocks

Printable View

04-23-2019, 02:16 PM
binar

VBA to relocate XML blocks

Fellow Forum Members,
I learned a NotePad++ regular expression is not the right tool for what I need to do. Therefore, I am hoping someone in this forum can tell me if an Excel VBA is the correct tool. I am using Excel for Office 365 v16. An "XML Block Relocator" is the best name I can think of for the VBA I need. Its purpose is to move within the same file an XML block to another location downstream in the XML code. Below is a summary of how I need the VBA to perform:

I need the VBA to fetch from an XML file list located in "Column A". The "Column A" list contains 300 XML files which are located in C:\XML_Source_Files. Once the VBA completes the change it saves the edited XML file to this directory, C:\output.

The actual change I need the VBA to perform is a cut and paste operation. The CUT operation requires the VBA to select an XML block by the opening

Code:

<referencedApplicGroup>

XML tag and closing

Code:

</referencedApplicGroup>

XML tag. Additionally, the CUT operation also needs to include the <data> tags in between as well (see example XML block shown below in the BEFORE state).

Code:

<upstream XML tag data> <applic> <referencedApplicGroup> <data> <para>Example Text</para> </data> <data> <para>Example Text</para> </data> </referencedApplicGroup> </applic> <downstream XML tag data>

With the cut operation complete I then need the VBA to PASTE the same XML block to a new location. The target location is in front of the opening

Code:

<content>

XML tag. The example below shows how the XML should like after the PASTE operation is complete (the AFTER state):

Code:

<upstream XML tag data> <referencedApplicGroup> <data> <para>Example Text</para> </data> <data> <para>Example Text</para> </data> </referencedApplicGroup> <content> <downstream XML tag data>

Can a VBA perform such an operation? I ask because I learned a Regular Expression cannot do it. Nevertheless, I would be very grateful if anyone out there would be kind enough to help me develop this VBA I call an "XML Block Relocator" because I do not have the advanced VBA skills needed to create such a VBA. I'm sure it will not only help me but also many of the members belonging to this forum who have a lot of TEXT edits to perform across hundreds of TEXT files. Thanks in advance for your help.
04-23-2019, 05:48 PM
p45cal

I don't know what I'm doing, something like:

Code:

Sub test() For Each cll In Range("A2:A300").Cells blah2 "C:\XML_Source_Files\" & cll.Value, "C:\output\" & cll.Value Next cll End Sub Sub blah2(myFullPathAndNameSource, myFullPathAndNameDestn) Dim FSO As Object Set FSO = CreateObject("Scripting.FileSystemObject") xx = FSO.OpenTextFile(myFullPathAndNameSource).ReadAll 'your source file pos1 = InStr(xx, "<referencedApplicGroup>") pos2 = InStr(xx, "</referencedApplicGroup>") newxx = Left(xx, pos1 - 1) & Mid(xx, pos2 + 24) insertText = Mid(xx, pos1, pos2 - pos1 + 24) pos3 = InStr(newxx, "<content>") newxxx = Left(newxx, pos3 - 1) & insertText & Mid(newxx, pos3) Set txtstr = FSO.CreateTextFile(myFullPathAndNameDestn, True, True) 'your destination file. txtstr.Write newxxx txtstr.Close End Sub

Run test
04-23-2019, 06:33 PM
binar

p45cal,
Thank you very much for your help. I am testing it out. However, I am curious to learn something about your code. What directs the VBA to paste the XML block to the left side of the <content> tag and not to the right side of the <content> tag? Again thank you very much for your help.
04-23-2019, 07:55 PM
binar

1 Attachment(s)

XML Block Relocator

Hi p45cal,
I did a test run and I got a "Run-time error76": Path not found error.

I changed code below:

xx = FSO.OpenTextFile(myFullPathAndNameSource).ReadAll 'your source file

to:

xx = FSO.OpenTextFile(C:\XML_Source_Files\).ReadAll 'your source file

and it did not fix the error.

I have attached Zip file containing my test XSLM file containing your VBA. I also included 4 XML test data files to apply the change to. My hope is these files better communicate the task I need the VBA to perform. I would be very grateful if you or anyone in this forum can help me developing this VBA. Thanks for the help and I look forward to seeing this VBA do its magic.

Attachment 24132
04-24-2019, 02:42 AM
p45cal

Quote:

Originally Posted by binar

Hi p45cal,
I did a test run and I got a "Run-time error76": Path not found error.

I did the same with your file and got the same error, however, did you check you output folder?
The line:
For Each cll In Range("A2:A300").Cells
runs through the cells of the active sheet from cell A2 to cell A300.
Your file has file names in cells A2, A3 and A4. When it gets to cell A5 is when my code errored. Cell A5 is blank.
Reinstate my original line:
xx = FSO.OpenTextFile(myFullPathAndNameSource).ReadAll 'your source file
and change the line
For Each cll In Range("A2:A300").Cells
to:
For Each cll In Range("A1:A4").Cells
Your folder names, source and destination, have to be exactly right with backslash characters in the right places and the source files must exist.
04-24-2019, 01:48 PM
binar

Hi p45cal,
Thank you for your post. I made the changes you posted and the error disappeared which is good news. However, the four XML test files contained inside the C:\XML_Source_Files folder did not change. The XML block remains in the same place and is not relocated next to the <content tag. Can you or anyone in this forum viewing this thread please suggest any other ideas so the VBA correctly relocates the XML block. Thank you in advance for any help.
04-24-2019, 02:18 PM
p45cal

Quote:

Originally Posted by binar

Hi p45cal,
Thank you for your post. I made the changes you posted and the error disappeared which is good news. However, the four XML test files contained inside the C:\XML_Source_Files folder did not change.

I wouldn't expect them to; you said:

Quote:

Originally Posted by binar

Once the VBA completes the change it saves the edited XML file to this directory, C:\output.

In my last message I said:

Quote:

Originally Posted by p45cal

I did the same with your file and got the same error, however, did you check your output folder?

Your code line:

Code:

blah2 "C:\XML_Source_Files\" & cll.Value, "C:\output\" & cll.Value

You need to look in the red folder for the results.
04-24-2019, 06:10 PM
binar

Quote:

Originally Posted by p45cal

I wouldn't expect them to; you said:

In my last message I said:
Your code line:

Code:

blah2 "C:\XML_Source_Files\" & cll.Value, "C:\output\" & cll.Value

You need to look in the red folder for the results.

Sorry, for my stupid mistake. I was at my job when I tested it out and was being so rushed over some other matter I forgot all about the C:\output\ detail I specified. Nevertheless, it was a happy surprise to open up the Output folder and find four XML files inside waiting for me. You are a VBA Maestro! :thumb

Thank you very much. I find it to be magical how the XML block has been relocated to the left side of the CONTENT tag.

Again, thank you very much for your VBA help. I am going to study it so I can try to understand the programming logic.