Excel XML Data Import via URL with Dynamic Query Parameters Not Working

[koyama]

(1)
I have been using XML Data Import via a static URL in Excel with no problems.

External data is pulled into a table in Excel, and when you refresh the table it refetches the XML at the remote URL.

For reference, the method is described here:
http://blogs.msdn.com/b/hovsep/arch...-and-import-data-from-an-xml-web-service.aspx

Now I want to use a more advanced dynamic URL with query parameters.
(The actual case is a bit different than below, but so people can better understand, I have created some examples.)

For example, I have 2 XML-files at these URLs:
http://koyama.dk/demos/MREXCEL001/xml.php?department=1
http://koyama.dk/demos/MREXCEL001/xml.php?department=2

The first URL holds employees in department 1, the second holds employees
in department 2.

In the below Excel file I have successfully pulled XML data from the first URL above using:
Data > Get External Data > from Other Sources > From XML Data Import
http://koyama.dk/demos/MREXCEL001/xml-data-source.xlsm

Problem:
Within the Excel file I want a drop-down so I can switch between employees from department 1 and 2.
The image shows what I am trying to achieve:

Question:
How do I instruct Excel to fetch data from ...department=2 when user selects "2" in drop-down and clicks "Go" ????


(2)
For comparison, it turns out I can achieve what I want using HTML output, element, together with the Web Query Data Import.

Data sources:
http://koyama.dk/demos/MREXCEL001/table.php?department=1
http://koyama.dk/demos/MREXCEL001/table.php?department=2

Excel file created using:
Data > Get External Data > From Web
http://koyama.dk/demos/MREXCEL001/web-query.xlsm

There is a macro which does the job of toggling between the two URLs

Sub switchDepartment()
    department = Cells(2, 4).Value
    With ActiveSheet.QueryTables(1)
        .Connection = "URL;" & "http://koyama.dk/demos/MREXCEL001/table.php?department=" & department
        .Refresh
    End With
End Sub

This would be fine, but the problem with this method is that I have XML data, not HTML. Also I can't seem to style the web query table with banded rows, for example.


(3)
I got excited when I learned about the technique where you can type the URL for web queries like this

http://koyama.dk/demos/MREXCEL001/table.php?department=["Department?"]

Excel will then prompt you to enter a value for department query parameter. You can then enter a value, or point to a cell reference where the value is located. With optional automatic refresh when cell value changes.

Method is described here:
http://dancingpenguinsoflight.com/2010/07/excel-web-query-urls-with-dynamic-parameters/

Now this could have solved the problem even without using VBA, but I found that it isn't working with an XML source. (No data is pulled even though I am prompted).

Question:
Why is URL syntax with parameter values in square bracketnot working for XML, only HTML?<table> </table><table> </table>

 

[Yhammouda]

Hello,
Beautiful post that describes exactly my problem. I spent the last two days looking for a solution...
Koyama, did you find a solution? Any help from others?
Thanx

 

[John_w]

Try using ActiveWorkbook.XMLimport as shown in the following code, which uses the 2 URLs in the OP's code. The first XML data URL is imported with the Destination argument specified (ActiveWorkbook.XmlMaps.Count = 0) and the second and any subsequent URLs are imported without specifying the Destination argument, thereby overwriting the existing imported data.

Sub Macro1()

    Dim i As Integer
    
    'Delete all XML maps - to establish clean test environment
    For i = ActiveWorkbook.XmlMaps.Count To 1 Step -1
        ActiveWorkbook.XmlMaps(i).Delete
    Next
    ActiveSheet.cells.Clear
    
    For i = 1 To 2
        If ActiveWorkbook.XmlMaps.Count = 0 Then
            'Import first XML data file, creating a new XML mapping
            ActiveWorkbook.XmlImport URL:="http://koyama.dk/demos/MREXCEL001/xml.php?department=" & i, ImportMap:=Nothing, Overwrite:=True, Destination:=Range("B4")
        Else
            'Import subsequent XML data files to existing mapping, overwriting existing data - don't specify Destination
            ActiveWorkbook.XmlImport URL:="http://koyama.dk/demos/MREXCEL001/xml.php?department=" & i, ImportMap:=Nothing, Overwrite:=True
        End If
        MsgBox "Next"
    Next

End Sub

 

[Yhammouda]

Yes i did something similar to your post, I read that in these articles:
1- XML to excel ==> How to Import Market Data into Excel Using XML
2- 2nd article to make it dynamic with parameters ==> Trading on Volatility? Use a Real-Time Option-Data Greeks Calculator

Thank you for the help ^^

 

[koyama]

Thank you to both Yhammouda and John_w for pointing me in the right direction.

Indeed it is possible to change the XML data source for an existing XML map. (Based on the second source provided by Yhammouda)

This shows the method:

ActiveWorkbook.XmlMaps("medarbejdere_Map").DataBinding.LoadSettings ("http://koyama.dk/demos/MREXCEL001/xml.php?department=2")
ActiveWorkbook.XmlMaps("medarbejdere_Map").DataBinding.Refresh

Or, even shorter syntax as found on page 105 in "Excel 2003 Programming: A Developer's Notebook" by Jeff Webb

ActiveWorkbook.XmlMaps("medarbejdere_Map").Import ("http://koyama.dk/demos/MREXCEL001/xml.php?department=2")

The method by John_w also works, but rather than deleting the existing XmlMap and importing the XML data from scratch with the .XmlImport method for the workbook, you can do the job using the .Import method for the existing XmlMap object.

To other people looking at this thread, the names of the XML maps are found in Excel via Developer > Source > XML Maps