Sending a web query powered file to other people?

[flightrisker]

Hello!

I'm sure this is a stupid question etc...

I've made a file that used a .iqy web query file to power the web query and to allow it to be customised based on the users choices. The query itself is as follows.

WEB
1
http://["Web Address"]


Selection=EntirePage
Formatting=All
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=True
DisableDateRecognition=True
DisableRedirections=True


Pretty impressive huh?


Anyway I've made this tool and would like to send it our to a wider audience. I'm a bit confused as to how I would go about this. I assume they need this web query file too? Sadly we're not all on the same network so they would need a local version. Would I need to email them all the excel file and the iqy file? Would this file need to be saved into the same place as I had mine (Desktop, classic).

Would it just pick this up or would they need to do anything to "re-connect" it as such?

And lastly, is there any simpler way of doing this?


Thanks!!

 

[John_w]

Yes, the way you have designed it, you need to distribute the .iqy file and the workbook. I don't know where the files would need to be saved on each user's computer, or if it would even work since the workbook might be looking for the .iqy file in a folder specific to your computer (e.g. your username). You would have to experiment to find out.

However, you shouldn't need to customise the web query with a .iqy file. Everything can be customised within the workbook itself. See WebQuery.

 

[flightrisker]

Hey John,

Thanks for the reply.

I've tried this but was having issues. The website I'm looking to draw in from is:
Holidays worldwide


The tool would allow you to select a country name from a dropdown and then see the holidays associated.
The country URLs are all like: Holidays and observances in Denmark in 2014 etc


I tried entering Holidays worldwide["Country"] as the query URL and then defining Country as a cell where the name is entered.

When I do this the URL resolved to Holidays worldwide[%22Country%22] and returns no data.


Any ideas?

 

[flightrisker]

Is it to do the the website itself? I tried to tutorial and it worked fine. I just can't get it to work for the example I have.

 

[John_w]

Having played around with a .iqy file, I was wrong about needing it. Once you have imported the web data with the .iqy file, all the settings are saved in the workbook. This means you only need to distribute the workbook and you can delete the .iqy file.

One difficulty with this particular site, Holidays worldwide, is that the country name must be in lower case, otherwise a 404 page not found error occurs. One way around this is to use the Worksheet_Change event to convert the cell value entered by the user to lower case before using it as the query parameter.

Private Sub Worksheet_Change(ByVal Target As Range)

    'Cell G1 contains country name entered by user
    
    If Target.Address = "$G$1" Then
        Application.EnableEvents = False
        With QueryTables(1)
            With .Parameters(1)
                'Set web query parameter - the country name - to lower case, as required by http://www.timeanddate.com/holidays/
                .SetParam xlConstant, LCase(Target.Value)
            End With
            
            'Suppress error returned by web query when an invalid country name is entered
            On Error Resume Next
            .Refresh False
            On Error GoTo 0
        End With
        Application.EnableEvents = True
    End If
    
End Sub

In this example I have used cell G1 on the web query sheet for the country name. When the user types a country name into this cell the above Worksheet_Change handler retrieves the web data using the entered country. A similar method should work with a Change event for an ActiveX combobox dropdown. Note that when setting up the web query parameter choose the 'Use this value' option and enter a valid country name, e.g. denmark.

 

[flightrisker]

Hey John

Thanks again for your reply and solutions.

I actually got around the lowercase thing in a less cool way, i just made a cell next to the country input cell that converts that cell to lowercase and then used that as the reference for the parameter.

I've deleted the web query file now and as you say it works. So I've no idea what those people that said it didn't work were on about. I guess it was user error.


Thanks a milion though.