Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Load xml file using VBA

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:
Dim oGPXdoc As MSXML2.DOMDocument60 (I have tried this with and without the 60)
Dim oGPXroot As MSXML2.IXMLDOMNode
Dim oGPXrte As MSXML2.IXMLDOMNodeList
Dim oGPXattributes As MSXML2.IXMLDOMNamedNodeMap
Dim oGPXrtename As MSXML2.IXMLDOMNode
Dim oGPXdocpe As MSXML2.IXMLDOMParseError



Private Sub cmdGPXstart_Click()
Dim lRteCount As Long
Dim lGPXready As Long

    tbGPXfilename = GPXfile
    If tbGPXfilename <> "" Then
        Set oGPXdoc = New MSXML2.DOMDocument60
        oGPXdoc.async = False
        oGPXdoc.validateOnParse = True
        If oGPXdoc.Load(tbGPXfilename) Then
            lGPXready = oGPXdoc.readyState
            Set oGPXroot = oGPXdoc.DocumentElement
            tbGPXXMLversion = oGPXdoc.XML
            Set oGPXrte = oGPXdoc.SelectNodes("gpx")
            Set oGPXdocpe = oGPXdoc.parseError
            If oGPXdocpe.ErrorCode <> 0 Then
                MsgBox (oGPXdocpe.reason & " Code: " & oGPXdocpe.ErrorCode)
            End If
            For lRteCount = 0 To oGPXrte.Length - 1
                Set oGPXattributes = oGPXroute.Attributes
                Set oGPXrtename = oGPXattributes.getNamedItem("rte")
            Next lRteCount
            oGPXdoc.Save ("new.xml")
        Else
            MsgBox ("Could not load " & tbGPXfilename)
            tbGPXfilename = ""
        End If
    Else
    End If
End Sub
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


Post your answer or comment

comments powered by Disqus
Hey all,

I'm looking for documentation to implement the ability to read and write data to an XML file using VBA.

Basically, I'm looking to store user-defined variables in the XML file that is then referenced by my VBA code. Default settings for my app for example would be stored in the XML file, but could be changed by the end-user with changes being written back to the XML doc.

Can anyone point me in the right direction on this?

I tried searching for XML in the forums but apparently the 3-letter acronym is too short to use as search criteria.

Thanks!

- Brian

Hello - I have had luck loading xml files using the following declaration and
assignment in the sub routine:

Set xmlDoc = CreateObject("Microsoft.XMLDOM")
xmlDoc.async = "false"
xmlDoc.Load ("G:CatTree.xml")

However, I have downloaded a sample excel file that makes the following
declaration in the declarations section of the module:

Dim catTree As MSXML2.DOMDocument

And later in the sub routine, the file is loaded with the following code:

Set catTree = New Msxml2.DOMDocument
catTree.async = False
catTree.Load (catTreeFileLocation)

What is the difference between the two ways of handling xml. The reason I
ask is that the 2nd example works just fine in the excel file that I
downloaded. But if I try to duplicate in any other file, I get a compile
error: "User-defined type not defined." at the module level declaration.
What could be the difference between the file I downloaded and a file I
create myself? I'm talking about duplicating the whole project so that there
is no difference... I still get the compile error.
--
Thanks,
Mike

hi, i am newbie to this environment.my requirement is i need to read xml file using vba macros and have to show the datas in excel.
i have one button in my excel sheet.if i click the button it has to read xml data which is n my "c:empdetails.xml" and has to show the datas in excel.please help me to this. can u please show some example to do this?

i was just trying to create an XML file using VBA but this error message displayed.. i hav highlighted the error line.


	VB:
	
 
Private Sub btnCreateXML_Click() 
     'Declare String variable called filename
    Dim FileName As String 
     
    FileName = InputBox("Enter File Name") 
    If FileName = "" Then 
        MsgBox ("Your action has been cancelled"), vbOKOnly 
    Else 
        FileName = "C:bla.xml" 
         
         'Constants and Variable Definitions for XML Code
        Const ForReading = 1, ForWriting = 2, ForAppeding = 8 
        Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0 
        Dim fs, f, ts 
         
         'File handling housekeeping tasks
        Set fs = CreateObject("Scripting.FileSystemObject") 
        fs.CreateTextFile FileName 
        Set f = fs.GetFile(FileName) 
        [B]    Set ts = f.OpenAsTextStream(ForAppending, TristateUseDefault)[/B] 
         
         'Write XML Code to textfile(extension xml)
        Sheets("Summary").Select 
         'XML COntrol Parameters
        ts.writeLine "" 
        ts.writeLine "" 
        ts.writeLine "" 
         'Loan Details
        ts.writeLine "" 
        ts.writeLine " Customer Name" 
        ts.writeLine "" & Sheets("Summary").Range("F4").Value & "" 
        ts.writeLine "" 
        ts.writeLine "" 
         
         'Close the textfile
        ts.Close 
        MsgBox "File Saved and Closed" 
    End If 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I need to open a text file with fixed length records and set up field lengths.

I need to be able to open a file using VBA. The file name will be loaded into a textbox on a user form and then entered into a cell.

How do I insert the file name into VBA???

Hi,

I'm using Excel 2000 and I wish to build a VBA macro which will compare
specific fields in a given .xml file to specific fields in a standard Excel
..xls file.
I know the VB / VBA which is needed to compare fields in 2 worksheets, but I
don't know how to open the .xml file using Excel 2000. Is that possible to
open xml file using Excel 2000 or I have to use higher version of Excel?

If it's not possible to do that in Excel 2000, can I use VB6 to do that
despite the fact that I don't have higher version of Excel? How can I use
VB6 to open the records/data in that XML file? Should I use ADODB for that?
Is that possible?
I know it's not a VB discussion group so if you direct me to a better group
for that question it's fine.

I'm using: Office 2000, Windows 2000 Pro, and Visual Studio 6.
I know VBA, some VB and a bit of API calls. I do NOT know VB.NET.

Kind Regards,
Amir.

Hi Everyone

I want to create a "save button" on the xls file using VBA. After the members fill in the ‘CBP - Peer Review Feedback’ tab, they should click on a ‘Save’ button" that will ensure mandatory fields are input.

Very much appreciated if anyone could help me on this, thanks

The password for the xls file is pradmin

L

Is this possible.

Or can i run other files using VBA script inside Excel.

I am trying to get the list(names) of all the folders in excel file using vba and then use the correct folder to open the required file "test file"
e.g the following folders are located in S:Ing FilesArc and each one of them contains subfolders JW and PER and then the "test file" inside JW folder.

FOLDER NAME:
12-11-2007_2111
08-08-2008_2139
09-08-2008_2130
10-09-2008_2140

so the "test file" is located in S:Ingenium FilesArchiveFOLDER NAMEJWPERtest file
Please note that test file is a txt file and I want to open it as an excel file.

Also once I get the list of all the folders on the excel file how can I then use them to open the required "test file". I want to open the "test file" to copy some data into the file where the macro is located.

Please also note that the file which contains macro is located in completely different drive say I drive whereas "test file" and all these folders are located in S drive

I will really appreciate if someone can please help me in this problem.

Thanks in advance
Zest

hi all
how can i write/read data to/from text file using VBA ??
is there any way
regards
wizzz

I wish to open a pdf file using vba. Does anyone know whether this can be done?

Appending a file using VBA

Hi,

Suppose I have two files. Q:BLOTTER and Q:TEST

How would append Q:TEST with all the rows from Q:BLOTTER?

Suppose Q:TEST ends at row 20.

I would like Q:BLOTTER to be copied to Q:TEST from row 22 onwards.

Thanks

Hi,

I have two files Book1 and Book2 (Book1.xls file path is "C:Book1.xls" and Book2.xls file path is "C:MacfolBook2.xls")

I am using VBA Code in Book1.xls and below shown code works only when the book2.xls is open and not when closed. So, can anyone please give me solution how to get the value from closed file using VBA Vlookup.

This Code Works only when the Boo2.xls file is open but my requirement is Book2.xls file should be closed but the values from it to be used without opening it.
MsgBox WorksheetFunction.Vlookup(Range("A2"), Workbooks("book2.xls").Sheets("sheet1").Range("A:B"), 2, False)

Thanks,
Raju A

Hi

I have a file in the working directory named as "postsearch.10". I would like to find out prefix of this file using VBA. I mean here the word before ".10" is "postsearch".

Can any body help me?

I would like to play a .fla (Flash file I think) file using VBA. I have
searched the internet fro ideas without=t much success.

Can anyone help?

Gary

I've some data in column A of my excel document I want to create a new XML file and copy/import/move this data into the new XML file using macro>.....

Hi,

I have columns A to AA filled with data down to row 7 with the headers always being in the first row. What I am trying to do is to load the XML map from an XML file, then assigning each element in the XML maps to a certain column and then exporting that XML file. I want to do all this is all in VBA. I was able to find out what the code is to load in the XML map using the macro recorder:

HTML Code:
Sub Macro1()
 
    ActiveWorkbook.XmlMaps.Add( _
        "vpnlfap00844udv00844m05e610Desktopxml_pos_sample.xml", "posFile"). _
        Name = "posFile_Map1"
 
End Sub

However, when I started assigning the elements of the map, the macro recorder did not record anything. Can anyone tell me what kind of code I would need to use to assign the elements of the map to the columns (first element to the first column, second element to the second column, etc, with the headers of the columns and the name of the elements always being the same)? I would really appreciate any kind of help!

I am trying to learn how to use xml with vba. I want to get the value of the "Location" element in my xml file. When I run my code I get the error "Error 91, Object Variable or With not set." Below is my VBA code and my xml file. I have also made a reference to MSXML 6.0. Thanks!

     Dim xmlDoc As New DOMDocument60
     xmlDoc.Load ("2010_Config.xml")

    Dim thisElement As Variant
    thisElement = xmlDoc.DocumentElement.nodeName
    
    Dim nextElement As Variant
    
     Dim myValue As Variant
    
    Do Until thisElement = "Location"
        nextElement = xmlDoc.SelectSingleNode(thisElement).ChildNodes.NextNode.nodeName
        myValue = xmlDoc.SelectSingleNode(thisElement).ChildNodes.NextNode.NodeValue
        thisElement = nextElement
    Loop
Here's my xml code.

<?xml version="1.0"?> 
	<Excel>
		<Chart ChartName = "Operational Availability">
			<Goal>0.8</Goal>
			<Location>"2010_OperationalAvailability.xlsm"</Location>
		</Chart>
	</Excel>


I'm able to import XMLs using OpenXML function in Excel VBA from Web folder/Intranet with the code below.


	VB:
	
 
Application.Workbooks.OpenXML _ 
Filename:="http://abc.astart.com/customers.xml", _ 
LoadOption:=xlXmlLoadImportToList 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Is there a way to import all XML files from a web URL/location ? Below code doesnt work to import all XML files.


	VB:
	
 
Application.Workbooks.OpenXML _ 
Filename:="http://abc.astart.com/*.xml", _ 
LoadOption:=xlXmlLoadImportToList 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have a client who developed a very simple MS Access application where he loads data for about 250 stores in a table and prints a report for each store. He wanted to print 250 different reports in pdf form, one for each store, and put the reports in the same folder with a different name for each store. I solved the problem by using VBA code to open the report in design mode, changing the Report caption to the desired report file name, and saving the report. Then I opened the report for print (he had set his PDF printer as the default printer) and applied the filter to limit the report to the desired store. I looped through all the stores till they were all "printed".

Now, I have another department with a similar request, however, his "database" is in Excel. Does anyone know how I can "print" the selected Print Area to the default printer with a specific file name.

Thanks, Eddie

I have some embedded PDF files in Excel file. I want to save them in a separate folder as seperate files, by pressing a button in toolbar.
How to do that using VBA?

Thanks

I am trying to run a macro to open an xml file in excel and replace the data records with new ones. I keep getting the message: "Run time error '1004' Method 'Range' of object '_global' failed". Here is the code I am using which I got by hitting "Record macro" in Excel and then walking through the steps I wanted it to do:


	VB:
	
 UseOpenXML() 
    Application.Workbooks.openXML _ 
    Filename:="I:OPERATIONSExtract Transform LoadCustomer Master Template.xml", _ 
    LoadOption:=xlXmlLoadMapXml 
    Range("Table[CUSTID_23]").Select 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The name of the Table is Customer_Master but there are many tables in the schema.

Any help would be greatly appreciated.

Stephanie

I want to merge two excel files (both have) only one excel sheet using VBA code. Can any one give me hint or help me out to carry out this task?

I am trying to create a template in Excel using VBA to open a Mappoint file in Excel. I have tried to record the macro while I Insert>Object>Create from file, but unfortunately it creates a macro that opens a specific file, while i would like it to open a directory. Any suggestions will be appreciated.

This is the VBA I recorded:

Sub Macro1()

ActiveSheet.OLEObjects.Add(Filename:= _
"Z:Lehman BrothersHealthcareProperty Maps2.ptm", Link:=False, _
DisplayAsIcon:=False).Select
End Sub


No luck finding an answer? You could always try Google.