Excel XML data channel that displays row data from a row

Question

I have an XML feed that draws a list of email addresses sorted alphabetically in Sheet 1

In sheet 2, I have a list of email addresses in column 1, and then a few other columns with user information.

When I update the data and add a new email, the list of email addresses in column 1 moves down, so the data in the other columns does not match. In any case, I can insert a new row and shift all the data in all columns if and when the new address is added to column 1. In the same way, delete the data row if the email is removed from the feed.

I know that this is really intended to work in a database, but I do not have such an opportunity.

Thanks in advance!

Answer 1

First of all, since there is no information, I will take the script for work and try to achieve what you need. You may need to tweak the code a bit to fit your needs, but that gives you a basis for working.

Since your XML feed does not contain additional data, a table containing email addresses will not add rows to the rest of the table as you want. My suggestion is that you use vba code to do this work.

Assuming you have the following xml file:

<?xml version="1.0" encoding="UTF-8"?> <Email> <address> [e-mail] </address> <address> [e-mail] </address> <address> [e-mail] </address> <address> [e-mail] </address> <address> [e-mail] </address> <address> [e-mail] </address> </Email> 

Using the following code, you will create a table in a worksheet:

 Const xmlFileUrl As String = "c:\filePath\note.xml" Sub ClearXmlMaps() Dim existingXmlMap As XmlMap For Each existingXmlMap In ActiveWorkbook.XmlMaps existingXmlMap.Delete Next existingXmlMap End Sub Sub CreateMailList() Dim xmlTable As XmlMap ClearXmlMaps Application.WindowState = xlNormal ActiveWorkbook.XmlImport URL:=xmlFileUrl, ImportMap:=Nothing, Overwrite:=True, Destination:=Range("$A$1") Set xmlTable = ActiveWorkbook.XmlMaps(1) xmlTable.Name = "EmailList" End Sub 

The code generated the red square part of the table below:

And I added a new column called "Name". Now suppose I want to update my XML stream without clearing all the column name information. To do this, I will use the auxiliary sheet (my main sheet here is “Data”, and the auxiliary sheet is the “Aux” sheet) to copy all the data, update the feed and finally replenish my table with the last state using the VLOOKUP command, for example, below:

 Sub RefreshEmailList() Dim existingXmlMap As XmlMap Dim dataSheet As Worksheet Dim auxSheet As Worksheet Set dataSheet = ThisWorkbook.Worksheets("Data") Set auxSheet = ThisWorkbook.Worksheets("Aux") dataSheet.Cells.Copy auxSheet.Cells(1, 1) auxSheet.Range(auxSheet.Cells(1, 1), auxSheet.Cells(auxSheet.Cells(auxSheet.Rows.Count, 1).End(xlUp).Row, auxSheet.Cells(1, auxSheet.Columns.Count).End(xlToLeft).Column)).Value = auxSheet.Range(auxSheet.Cells(1, 1), auxSheet.Cells(auxSheet.Cells(auxSheet.Rows.Count, 1).End(xlUp).Row, auxSheet.Cells(1, auxSheet.Columns.Count).End(xlToLeft).Column)).Value dataSheet.Range(dataSheet.Cells(2, 2), dataSheet.Cells(dataSheet.Cells(dataSheet.Rows.Count, 1).End(xlUp).Row, dataSheet.Cells(1, dataSheet.Columns.Count).End(xlToLeft).Column)).Clear For Each existingXmlMap In ThisWorkbook.XmlMaps If existingXmlMap.Name = "EmailList" Then ActiveWorkbook.XmlMaps("EmailList").DataBinding.Refresh End If Next existingXmlMap dataSheet.Range(dataSheet.Cells(2, 2), dataSheet.Cells(dataSheet.Cells(dataSheet.Rows.Count, 1).End(xlUp).Row, dataSheet.Cells(1, dataSheet.Columns.Count).End(xlToLeft).Column)).FormulaR1C1 = "=IFERROR(IF(VLOOKUP([@address],Aux!C1:C,COLUMN(),FALSE) = 0 , """", VLOOKUP([@address],Aux!C1:C,COLUMN(),FALSE)), """")" dataSheet.Range(dataSheet.Cells(2, 2), dataSheet.Cells(dataSheet.Cells(dataSheet.Rows.Count, 1).End(xlUp).Row, dataSheet.Cells(1, dataSheet.Columns.Count).End(xlToLeft).Column)).Value = dataSheet.Range(dataSheet.Cells(2, 2), dataSheet.Cells(dataSheet.Cells(dataSheet.Rows.Count, 1).End(xlUp).Row, dataSheet.Cells(1, dataSheet.Columns.Count).End(xlToLeft).Column)).Value End Sub 

If you change the xml file to the following:

 <?xml version="1.0" encoding="UTF-8"?> <Email> <address> [e-mail] </address> <address> [e-mail] </address> <address> [e-mail] </address> <address> [e-mail] </address> <address> [e-mail] </address> <address> [e-mail] </address> <address> [e-mail] </address> </Email> 

And then run the RefreshEmailList () routine, you will get the result below:

The solution above works to add or remove rows, but you should know two things:

• Updating an XML feed using the refresh button will not result in an event
• This solution does not preserve the old states, which means that if one e-mail is deleted and then added again, information about this user may be lost.

Answer 2

Instead of automatically updating the column, you can place incoming data in the "waiting area" (either a separate worksheet or an array, depending on which is more convenient for you).

You can then compare the two lists to check for new email addresses (add them to the end of existing data) or email addresses that no longer exist in the incoming data (delete lines from existing data).

Answer 3

To insert a new row (and shift each column down), you can use this VBA code:

 ActiveCell.EntireRow.Insert xlShiftDown 

when ActiveCell is the cell (range) in the row where the new one should appear: if you need to add the 5th empty row ActiveCell.Row should be = 5.

To delete the entire line, the code will be:

 ActiveCell.EntireRow.Delete xlShiftUp 

when ActiveCell is a cell (range) in a row to delete