Note: This document was originally written by Rakesh Mehta, I've just cleaned it up a bit...
Hi Guys, I saw this question asked many times in the forums so thought I would do a little write up on it so all can take little benifit of the trick.
In my example I will use a freely available webservice from http://www.cdyne.com/ called - weather forecast webservice. The base URL for this webservice is http://wsf.cdyne.com/WeatherWS/Weather.asmx. The method I will use in my explaination is "GetCityWeatherByZIP" method. This method returns weather forecast of next 7 days for a given US zip code.
We all know that one way to get data consuming a webservice is by building a Custom Data DLL interface (eg salesforce example from QlikTech), which werks perfectly but surely requires extra programming abilities like in C++, etc.
What I have found that a webservice can also be consumed (or invoked) using 3 ways:
- A POST command that embeds a SOAP request
- A POST command that specifies the method name and parameters
- A GET command whose URL contains the method name and parameters
In my example, I have used the third method - A GET command whose URL contains the method name and parameters. Below is how I programmed it:
- Call the URL http://wsf.cdyne.com/WeatherWS/Weather.asmx?op=GetCityWeatherByZIP in Internet Explorer, and put a valid US zip code. Press "Invoke" button. This will return you an XML file with weather forecast of that zip code of next 7 days. Copy all the xml data (CNTL-A + Copy) and Paste it in a notepad session. Save this file as temp.xml somewhere on your PC.
- Open a new QVW file, and in the script editor press "XLM Files" button. Locate the file you saved in step 1 and go through the wizard. This will put a script to load the data. Go ahead and remove the path from the file name, leaving just the file name. By doing 1 & 2 steps, now you have a valid load statement with correct table and field names.
- Open VB Macro script editor and Place this script:
Sub ws Const HOST = "http://wsf.cdyne.com/WeatherWS/" Const URL = "Weather.asmx" 'Create xmlhttp opject Set xmlhttp = CreateObject("Microsoft.XMLHTTP") 'Get the selected zip code set fld=ActiveDocument.GetField("ZipCode") 'If user has selected more than one zip code, show error if fld.GetValueCount(1) <> 1 then msgbox ("Please select only one Zip code") exit sub end if 'Finally, get the selected zip code value set val=ActiveDocument.Fields("ZipCode").GetPossibleValues zip = trim(val.Item(0).Text) 'Call HTTP Get method passing WS URL and parameters xmlhttp.open "GET", _ HOST & URL & "/GetCityForecastByZIP?ZIP=" & zip, false xmlhttp.send "" 'Create a temporary file to save the results from WS set Path = ActiveDocument.Variables("vPath") FileName = Path.GetContent.String & "XMLOutput.xml" set fso = CreateObject("Scripting.FileSystemObject") set s = fso.CreateTextFile(FileName, True) s.writeline(xmlhttp.responseText) s.Close() 'Load document with the temp file ActiveDocument.PartialReload 'Delete the temp file fso.DeleteFile(FileName) end sub
- Now you need to make a way to load all possible US zip codes in the document so the user can select a zip code. I have loaded list of zip code from a URL I found while googling - http://www.census.gov/tiger/tms/gazetteer/zips.txt . You can always put an input box and let user type a zip code, and then move this zip code in a variable and then use that variable in the VBScript to pass to the WS URL.That's it. Try it with the example QVW file I have put in "Share QlikViews" section. Before you press button to get data, please make sure to give proper system access in the VB script section for macros to run through.
Now, would I use this method for importing lots of data from the internet? May be not. For that I would rather build a custom DLL. But to get little things, like current stock quote, or some currency exchange rates, etc. I think this method can be utilized.
Anyway, something to share. Have fun! Comments / suggestions are welcome.