XMLImport and ImportMap Using VBA

Question

Here is my issue..

I currently have a code built so I can import my XML data via the URL. The problem is when I use this code it drops off any columns that have no data. I need those columns to be included because I have pre-set formulas that populate other sheets with data. I figured I would need an XML mapping in order to do so. I then created a code so the XML Mapping is brought into the file via the XSD URL. After walking through the VBA step by step, I noticed the correct XSD mapping is brought into the file via the code below:

Dim strRestXSD As String 'References URL to retrieve XSD
Dim oMap As xmlmap
Set oMap = ActiveWorkbook.XmlMaps.Add(strRestXSD)

The problem is within the code below. I use the code below to then extract the XML, using the string attached to the XML URL. For whatever reason, the ImportMap is ignored when wanting to using the XMLMap identified above. The code below actually creates a new map and ignores the map which is created using the code above.

Dim strRestURL As String XML data

ActiveWorkbook.XmlImport URL:=strRestURL, ImportMap:=oMap, Overwrite:=False,
Destination:=Range("A$1" & lngRow)

Is anyone aware of a way I can import my XML data without losing the empty columns? Or are you aware of a way I can map my XML map to the XML Import?

Thanks in advance for your help!

Ali Akbari

Answer 1

Setting Destination:=Nothing should get the data to import into the existing map. Also, you may want to set Overwrite:=True; otherwise, the data in the existing mapped table will be appended to.