Followup to Using Excel 2003 XML features for a Poor Mans Schema Generator Post

Published 30 March 05 03:20 PM | chris 

One reader sent me a valid question regarding my post on the poor man’s version of a schema generator. He wants to know programmatically how he can import XML without gettting the following message from Excel:

 Poormanxml2

 Now can programmatically import using the following VBA command:

    ActiveWorkbook.XmlImport URL:="books.xml", _
        ImportMap:=Nothing, Overwrite:=True, Destination:=Range("$A$1")

Given a XML file on the local hard drive or available remotely via HTTP, you can import the XML and Excel will generate the schema for you. It turns out the ImportXML method is flexible. You can also import directly into a Map, or a new map will be created, overwrite existing data and indicate where the data should be imported into the workbook.

However, I could not find a way to programmatically prevent this dialog box from appearing. Of course, you could just check the box that says don’t pull it up again and it wont. I can’t help but wonder if there is a way around this. Anyone know of a solution? 

 

 

Comments

# PMorganCA said on November 5, 2008 10:41 AM:

I'm experiencing a bit of frustration with this myself and I don't even see the check box that says don't pull it up again.

I'm logging onto an API that replys with a session token in an XML tag. Then I use the token for subsequent calls that pull the data I need into tables.

I initially created the XmlMaps by pulling the data from the site manually: Excel generously created the maps for me and I renamed them. My problem in VBA macros is that when I run this:

ActiveWorkbook.XmlImport _

   URL:="http://some.site.com/api.asp?cmd=list&token=" & token, _

   ImportMap:=ActiveWorkbook.XmlMaps("MyMap")

Excel pops up that dialog asking me which map to use, even though its specified in the ImportMap argument.

If I try to ignore the map that I made:

ActiveWorkbook.XmlImport _

       URL:="http://some.site.com/api.asp?cmd=list&token=" & token, _

       ImportMap:=Nothing, _

       Overwrite:=True, _

       Destination:=Range("xmlResponse")

then Excel seems to ignore the Overwrite argument and it bombs out with an error that complains about overlapping the data from a previous map.

In another attempt, Excel stored the data but inserted it, moving columns right and rows down.

Sorry I couldn't provide you a solution, but I'll definately be watching the thread for any further comments

# PMorganCA said on November 26, 2008 9:51 AM:

I just fixed my problem... maybe this info will help:

in addition to the Workbook.XmlImport method, there's an XmlMap.Import method. If I use the latter, I don't get prompted to pick an XML Map.

this works for me with no user interaction:

Sub SetFilter(token As String, filter As String)

'

'

''''''''''

   Dim urlStr As String

''''''''''

   urlStr = "http://server.company.com/api.asp?" & _

                "cmd=saveFilter&" & _

                "sFilter=" & filter & _

                "&token=" & token

   With Workbooks("Destination Workbook.xlsm")

       .Activate

       Worksheets("Destination Sheet").Activate

       .XmlMaps("setFilterRespMap").Import urlStr

   End With

End Sub

Anonymous comments are disabled