Load xml file using VBA

**jstevens** · 07-13-2010, 01:08 PM

I have been attempting to load a very simple xml file into the DOMDocument object using Excel 2007 VBA and MSXML version 6.0. I can load and parse the file using XML/Pro with no problems. When I load the file using the Load method, the method returns a true value, but nothing is loaded in the object. I do get a parse error status in the object regardless of whether or not I set the ValidateOnParse property to true or false. XML/Pro loads and validates the file without any errors.

The following is the relevant code:

Please Login or Register  to view this content.

The program always falls through the "if oGPXDoc.Load..." statement, but there is nothing in the oGPXDoc object, except the parse error code of -1072896760, "Invalid Character in text content".

I get this same error on several different xml files, all of which can be loaded by XML/Pro without errors.

I'm lost. Any help would be greatly appreciated.

John

**Leith Ross** · 07-13-2010, 01:34 PM

Hello jstevens,

Welcome to the Forum!

To make your posts easier to read, copy, and edit please wrap your code. I did it for you this time. Here is how you can do it next time.

How to wrap your Code using the # icon
1. Select all your code using the mouse.
2. Click on the # icon on the toolbar in the Message window. This will automatically wrap the text you selected with the proper Code tags to create a Code Window in your post.

To manually wrap your code, use these Bulletin Board Code Tags
[code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] at the end of the last line.

To learn more about BB codes used in this forum, just click on the link below...

Bulletin Board Codes Tags

**jstevens** · 07-13-2010, 02:32 PM

I have at least partially solved this problem. I was using Application.FileDialog(msofiledialogOpen) to get the file string. This produces a full file name with path. The file is in the same directory as the VBA workbook and this seems to have produced the error. When I hard coded the file name ("Catalogue.xml") in the load statement the document object is loaded.
I guess the Load method assumes the file path is relative to the folder of the application. Anyway I can now load the file and start on the rest of the program.

**jstevens** · 07-15-2010, 07:30 PM

The real answer appears to be I was passing the file name to the load method from a text box on the userform. When I switched to using a string variable all of the xml file loading issues went away. Must be something different in the way a string is presented from a text box and a string variable.

**shg** · 07-15-2010, 07:41 PM

Thanks for posting back.

If you post you 'was' and 'is' code, maybe someone can explain why was went awry.

**jstevens** · 07-16-2010, 12:10 PM

Here is the working code:

Please Login or Register  to view this content.

The GPXfile subroutine returns the full file name of the xml file selected using the Application.FileDialog(msoFileDialogOpen) method. In the previous code (see initial post) the file name was placed in the text box, tbGPXfilename and that value was used in the oGPXdoc.load method. In the code above the filename string is placed in a string variable, sGPXfn, and that variable is referenced in the load method. This works everytime no matter in what folder the target file is located. The former worked only when I replaced the text box reference in the load method with the hard coded file name with no path (e.g., "fn.xml") and the file was located in the same folder as the VBA workbook.