Update Excel Spreadsheet with Data from XML File
Posted on 2013-11-20
I have an XML file that is generated by an Access database. I need to update the existing rows with specific data from the xml file.
The Excel columns are
A - LnName
B - LnID
C - SiteInfo
D - Client
The fields in the XML file I need are
SiteInfo and Client
I need to look up the LnID from column B in the XML file and update columns C and D with the corresponding data from the XML file. Similar to using a lookup formula.
I tried just importing the data from the XML and using an offset/match formula... however, when I do that the data from the xml file loses it's formatting even though it looks right in the sheet that I imported the file to.
So instead of looking like the below...
url - xxxxxxx
gd - xxxxxxx
dt - xxxxxxx
The data comes in like this...
url - xxxxxxxgd - xxxxxxxdt - xxxxxxx
I am somewhat familiar with VBA, but am definitely a novice, so any help you can provide would be awesome!