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

Private Sub CommandButton1Import_Click()
    Dim objMapToImport As XmlMap
    Dim CurrMap As ListObject
    Dim NewMap As ListObject
    Dim CurrWorksheet As Worksheet
    Dim NewWorksheet As Worksheet
        
‘ Find the handle for the xmlmap I am interested in      
    Set objMapToImport = ActiveWorkbook.XmlMaps("CostDetailTool_Map")

‘ Find the handle for the worksheet that has the data I am interested in      
    Set CurrWorksheet = ActiveWorkbook.Worksheets("IPVPN Links")
    
‘ Find the listobject on the worksheet I am interested in    
    Set CurrMap = CurrWorksheet.ListObjects(1)
    
‘Create a temporary worksheet and listobject to copy the data to     
    Set NewWorksheet = ActiveWorkbook.Worksheets.Add
    Set NewMap = NewWorksheet.ListObjects.Add
    
‘Set the data

    Set NewMap = CurrMap


End Sub

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