Consulting

Results 1 to 8 of 8

Thread: VBA to relocate XML blocks

  1. #1

    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
    <referencedApplicGroup>
    XML tag and closing
    </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).


        <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
    <content>
    XML tag. The example below shows how the XML should like after the PASTE operation is complete (the AFTER state):


        <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.

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    I don't know what I'm doing, something like:
    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
    Last edited by p45cal; 04-23-2019 at 06:10 PM.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    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.

  4. #4

    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
    Attached Files Attached Files
    Last edited by binar; 04-23-2019 at 08:52 PM. Reason: Changed ZIP file

  5. #5
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by binar View Post
    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.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  6. #6
    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.

  7. #7
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by binar View Post
    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 View Post
    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 View Post
    I did the same with your file and got the same error, however, did you check your output folder?
    Your code line:
    blah2 "C:\XML_Source_Files\" & cll.Value, "C:\output\" & cll.Value
    You need to look in the red folder for the results.
    Last edited by p45cal; 04-24-2019 at 02:29 PM.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  8. #8
    Quote Originally Posted by p45cal View Post
    I wouldn't expect them to; you said:

    In my last message I said:
    Your code line:
    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!

    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •