XML Importing

Hi All,

I am using XML to integrate data in a SQL database and data that is in excel. My key problem is maintaining data integrity in the database with what the user can change in the spreadsheet. I am trying to create some code that will effectively do a “diff” on the existing data in the spreadsheet XMLMAP and the new data in the database that will be imported.

The logic is therefore to reload an XML file into a spreadsheet to check what has changed in the spreadsheet compared to what is being loaded. There are some limitations to Excel and XML as some of you probably know. The key limitation is that an XML import is effectively a bulk load of data and therefore I have to create a copy of what is currently in the spreadsheet in order to compare to the new imported data and apply the changes that have been made to the new XML.

I have just started using VB and have coding skills that are about 10 years old. So I am struggling with the language as well.

Of course there are multiple ways of doing this and have attempted several of them
1. Attempt to query the XML on the spreadsheet using XPath to create a copy into another table / spreadsheet before importing the new XML. I gave up on this approach because I was getting XPath errors when I was using the XMLDataQuery. The need to also use a different language was also a little overwhelming so I sought to try another approach.
2. Second approach was to use a listobject as the imported XML exists within a list object in a worksheet in a workbook, aka the containment hierarchy. I have cut this code below but am having issues. These are
a. I am confused about how to access the object through the containment hierarchy. As there could be multiple list objectives on a spreadsheet, I am trying to reference the listobject that has the xmlmap and not others. Thus there are two hierarchies, one from the workbook to the XML and the other from the worksheet to the listobject.
b. In my test code I have been able to find the xmlmap via an index as there is currently only one listobject in my test code. However, I am unable to assign the instance of the xml listobject to a new listobject to take a copy of the previously imported data. This seems to be anti objected oriented to me.

Skippets of the code are below

Please Login or Register  to view this content.

Result of this is a clean run of code but the map do not copy over.

What is my best approach with this?

Do I give up on the listobject approach and learn Xpath to access data in the map. Is there another approach? Do I need to go back to VB school? Ahhrrr

Please help if you can… Thanks in advance

vbexcelnewbie

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

Added this time