Use VBA to insert large XML file into another

Question

I have used VBA to create an xml file and I have another 4mb xml file that I need to add to this. Is there a way to embed the file in excel and then use VBA to attach it to the created XML file?

I need the xml to be part of the excel file so the one that needs to be attached so it can always be found by the program and so that other people who use it don't need to locate the additional file.

If that's not possible, is there a way to have the user browse for the file and then append it before

Print #1, "</Folder>"
Print #1, "</Document>"
Print #1, "</kml>"

Close #1

Do you need the XML file to be an embedded object in the file, or do you need the XML contents to be literally in the cells of a worksheet? Or is it more concise to say that you ultimately have two XML files and you need to append one to the other? — David Zemens
– David Zemens, Commented May 29, 2014 at 14:48
I do have two XML files, one created with the VBA program and one that already exists. I need to add the outside file before I put the ending tags on the VBA created XML file. — Michael St Clair
– Michael St Clair, Commented May 29, 2014 at 14:58
OK. I would probably try to work with these two files separately as XML documents -- not as XML files opened in an Excel workbook(s). Give me a few and I will put together a brief example... — David Zemens
– David Zemens, Commented May 29, 2014 at 15:05

Blackhawk · Accepted Answer · 2014-05-29 15:34:59Z

2

OK, so I'm assuming that you have two valid XML files, both with opening/closing XML tags, and that you need to extract the second file's child nodes (all child nodes, so basically the full file except for the open/close XML tags) and append to the first file.

This is a simple example, and if you need to append individual child nodes in specific places, you will need to add logic/conditions to do as you need.

Close both files. Open a new workbook and create a vba procedure like so:

Sub AppendXMLFiles()
'requires reference to Microsoft XML, v6.0'
'requires reference to Microsoft Scripting Runtime'
Dim file1 As New MSXML2.DomDocument
Dim file2 As New MSXML2.DomDocument
Dim appendNode As MSXML2.IXMLDOMNode
Dim fso As New Scripting.FileSystemObject

'## Load your xml files in to a DOM document'
file1.Load "c:\users\david_zemens\desktop\example xml file.xml"
file2.Load "c:\users\david_zemens\desktop\another xml file.xml"

'## iterate the childnodes of the second file, appending to the first file'
For Each appendNode In file2.DocumentElement.ChildNodes
    file1.DocumentElement.appendChild appendNode
Next

'## View the new XML in the immediate window'
Debug.Print file1.XML

'## Write the combined file to a NEW file'
'   note: if the specified filepath already exists, this will overwrite it'
fso.CreateTextFile("c:\users\david_zemens\desktop\combined xml file.xml", True, False).Write file1.XML

Set file1 = Nothing
Set file2 = Nothing
Set fso = Nothing
End Sub

edited May 29, 2014 at 15:34

Blackhawk

6,1804 gold badges31 silver badges60 bronze badges

answered May 29, 2014 at 15:28

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Michael St Clair Over a year ago

I have already edited out the headers of the second file so it just needs to be inserted in. Would it be possible to have the user browse for the file and then append it before Print #1, "</Folder>" Print #1, "</Document>" Print #1, " </kml>"Close #1in my VBA code. That way it all runs in a single execution

Michael St Clair Over a year ago

Sorry, I've just been modifying based on what seems to be the most sane way to implement

David Zemens Over a year ago

"How can I modify code that creates an XML file to add lines from another xml file before the closing </Folder> tag." is a very different question from "how to I append some XML to another XML?" Yes, it is possible to do what you're asking now... use FileSystemObject to read the second file, and insert before closing </Folder> tag. Please try that, and post your code if you have trouble with it. I'm not writing another answer from scratch.

David Zemens Over a year ago

The most sane way to work with XML files is with an XML DOM parser :) not to manually break one of the files (by removing the tags) and then try to read it as text and print it in to another file... My point is that I gave an answer based on the description of the problem, but now you have significantly altered the problem to require a new solution...

Michael St Clair Over a year ago

I started a new question because my question did change, stackoverflow.com/questions/23938538/print-text-file-in-vba

|

Collectives™ on Stack Overflow

Use VBA to insert large XML file into another

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related