External Data with additional columns

[DGMan]

Dear all,

Recently I have come across a problem regarding the use of pulling external data from XML files to Excel 2010.

I work for a very small hotel. The hotel database we are using is very poor by any standard, so we cannot but make up its deficiencies by pulling some of its data to Excel.

The pulled external data basically has 2 columns, Room and Guest, in Excel table. The third one, "Special Needs", is the extra column I added for entering supplementary information about the guest. As an example shown below, the special needs of guests A and C have been manually entered:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Room[/TD]
[TD]Guest[/TD]
[TD]Special Needs[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]A[/TD]
[TD]No calls after 21:00[/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD]B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]104[/TD]
[TD]C[/TD]
[TD]Need 1 extra pillow[/TD]
[/TR]
</tbody>[/TABLE]

The next day, Guest X of Room 103 checked in. After refresh, so unexpectedly, the result is something like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Room[/TD]
[TD]Guest[/TD]
[TD]Special Needs[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]A[/TD]
[TD]No calls after 21:00[/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD]B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]103[/TD]
[TD]X[/TD]
[TD]Need 1 extra pillow[/TD]
[/TR]
[TR]
[TD]104[/TD]
[TD]C[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Instead of sticking to Room 104, the manually entered information sticks to the third row where the information was originally entered.

Several days later, Guest A of Room 101 checked out. Now my table is something like this after refresh:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Room[/TD]
[TD]Guest[/TD]
[TD]Special Needs[/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD]B[/TD]
[TD]No calls after 21:00[/TD]
[/TR]
[TR]
[TD]103[/TD]
[TD]X[/TD]
[TD]Need 1 extra pillow[/TD]
[/TR]
[TR]
[TD]104[/TD]
[TD]C[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The special needs of guest A didn't deleted along with the guest.

May I ask how I can remedy this situation? Thanks for your advice in advance.

 

[John_w]

Welcome to MrExcel forums.

Rather than doing a manual Refresh to update the data with the current XML file, try this macro which imports the current XML file and keeps the data in the Special Needs column in the correct row.

Create a copy of your workbook and put the code in a standard module and save the workbook as a macro-enabled workbook (.xlsm file). The macro expects the data to be in the first sheet (the Worksheets(1) in the code).

Option Explicit


Public Sub Update_XML_Data()

    Dim masterListObj As ListObject
    Dim tempSheet As Worksheet
    Dim tRow As ListRow, newRow As ListRow
    Dim RoomNumber As Variant, Guest As Variant
    Dim masterRow As Variant
    Dim i As Long
    
    With ThisWorkbook
        Set masterListObj = .Worksheets(1).ListObjects(1)
    
        'Add a temporary sheet and import the current XML data file into it
        
        Set tempSheet = .Worksheets.Add(after:=.Worksheets(.Worksheets.Count))
        .XmlImport Url:=masterListObj.XmlMap.DataBinding.SourceUrl, ImportMap:=masterListObj.XmlMap, Overwrite:=False, Destination:=tempSheet.Range("A1")
    End With
    
    'Mark rows in master table which will be deleted.  This adds a 4th column in the master data, which will be deleted later
    
    For Each tRow In masterListObj.ListRows
        tRow.Range(1, 4).Value = "Delete"
    Next

    'Loop through rows in newly imported XML data
    
    For Each tRow In tempSheet.ListObjects(1).ListRows
        RoomNumber = tRow.Range(1, 1).Value
        Guest = tRow.Range(1, 2).Value
        Debug.Print RoomNumber, Guest
        
        'Find this Room number in the master table
        
        masterRow = Application.Match(RoomNumber, masterListObj.DataBodyRange.Columns(1), 0)
        
        If IsError(masterRow) Then
        
            'Room number not found, so insert a new row in master table and copy current data to it
            
            Set newRow = masterListObj.ListRows.Add
            newRow.Range(1, 1).Value = RoomNumber
            newRow.Range(1, 2).Value = Guest
            newRow.Range(1, 4).Value = "Inserted"
            
        Else
        
            'Room number found, so update its row with current data
            
            masterListObj.DataBodyRange(masterRow, 1).Value = RoomNumber
            masterListObj.DataBodyRange(masterRow, 2).Value = Guest
            masterListObj.DataBodyRange(masterRow, 4).Value = "Updated"
            
        End If
        
    Next
    
    'Delete rows in master table which are marked as 'Delete', i.e. have not been newly inserted or updated
    
    For i = masterListObj.ListRows.Count To 1 Step -1
        If masterListObj.ListRows(i).Range(1, 4).Value = "Delete" Then
            masterListObj.ListRows(i).Delete
        End If
    Next
    masterListObj.ListColumns(4).Delete
    
    'Sort master table by Room number (column A)
    
    With masterListObj
        .DataBodyRange.Sort Key1:=.Range(1, 1), Order1:=xlAscending, Header:=xlGuess, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    End With
    
    'Delete the temporary sheet into which the current XML data file was imported
    
    Application.DisplayAlerts = False
    tempSheet.Delete
    Application.DisplayAlerts = True
    
End Sub

 

[DGMan]

Dear John,

Although I don't have the time now to try out your code, I wish to thank you for the time you have spent in writing this for me. I will get back to this forum if I have further questions. Thanks.

 

[DGMan]

Dear John and others,

I understand the logic behind your code, thanks a lot. But I have a question ....

Actually my real XML-imported table (the "masterlistobj") has a lot more columns than the one I have mentioned, and after the import, I have changed its column order. How can I preserve its column order while I import its data into the tempsheet? I am not so familiar with XML, so thanks a lot for your assistance in advance.

 

[John_w]

I haven't done much XML, but to import the XML data in a specific column order and keep that order when you refresh the XML data, I think you need to create an XML schema (.xsd file) and XML map for the XML data. See https://support.office.com/en-gb/ar...-XML-Map-ddb23edf-f5c5-4fbf-b736-b3bf977a0c53.

Aside from a VBA code solution, it might be possible to define the 'Special Needs' column as a column in the schema and map, and hopefully Excel will leave that column unchanged when it imports (refreshes) the XML data which doesn't contain that column.

 

[DGMan]

Thanks very much for your idea. I will try that.

 

[Fazza]

maybe also refer https://www.mrexcel.com/forum/excel...anually-entered-data-external-data-query.html
which I also posted as a response to a similar question two minutes ago, btw