Changing data in the web address of a web query by input

[ianccy]

I have a query with web address like this, http://www.xxxxxxxx.com/Example/cgihistory.cgi?id=1111&begin=2001&end=2008

Is there a way when I input a 4 digit no. (eg. 2222) in cell A1, the value in theweb query address (1111) is changed to (2222),

http://www.xxxxxxxx.com/Example/cgihistory.cgi?id=2222&begin=2001&end=2008

 

[Andrew Poulsom]

You can use the HYPERLINK function like this:

=HYPERLINK("http://www.xxxxxxxx.com/Example/cgihistory.cgi?id="&A1&"&begin=2001&end=2008")

 

[ianccy]

sorry Andrew, the address is in the Edit Query which i made to get data from the webpage, it is not possible to enter that in the address field, possible to use a VBA code to do this?

 

[Andrew Poulsom]

Like this?

ActiveSheet.QueryTables(1).Connection = "URL;http://www.xxxxxxxx.com/Example/cgihistory.cgi?id=" & Range("A1").Value & "&begin=2001&end=2008"

 

[ianccy]

how does it work? can you make a complete code?

Let me describe again what I am doing,

in cell B2, select Data > Get External Data > New Web Query > Enter the address in the web address field (http://www.xxxxxxxx.com/Example/cgihistory.cgi?id=1111&begin=2001&end=2008)

but I will have to edit the query everytime when I am searching a new data (2222 etc), I am thinking of by just entering the 4 digit value (2222) in cell A1, the address in Web Query will change by itself and search the web for the data,

possible?

 

[Norie]

ianccy

To get code why not record a macro (Tools>Macro>Record New Macro) when you do the Data> etc manually?

 

[ianccy]

I tried recording with macro, but the query field cannot be recorded,

 

[Norie]

I just recorded this.

    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.xxxxxxxx.com/Example/cgihistory.cgi?id=1111&begin=2001&end=2008" _
        , Destination:=Range("B2"))
        .Name = "cgihistory.cgi?id=1111&begin=2001&end=2008"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlAllTables
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .Refresh BackgroundQuery:=False
    End With

 

[Andrew Poulsom]

how does it work? can you make a complete code?

Let me describe again what I am doing,

in cell B2, select Data > Get External Data > New Web Query > Enter the address in the web address field (http://www.xxxxxxxx.com/Example/cgihistory.cgi?id=1111&begin=2001&end=2008)

but I will have to edit the query everytime when I am searching a new data (2222 etc), I am thinking of by just entering the 4 digit value (2222) in cell A1, the address in Web Query will change by itself and search the web for the data,

possible?

Right click the sheet tab and choose View Code. Paste this into the window on the right:

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Address <> "$A$1" Then Exit Sub
   With ActiveSheet.QueryTables(1)
      .Connection = "URL;http://www.xxxxxxxx.com/Example/cgihistory.cgi?id=" & Range("A1").Value & "&begin=2001&end=2008"
      .Refresh
   End With
End Sub

Press Alt+F11 to return to your worksheet. When you change the number in A1 your Query Table Connection will change and the query will be refreshed.

 

[ianccy]

With ActiveSheet.QueryTables(1)

this line is having error when I enter the data to A1,