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]

I thought you said you had added a QueryTable, so I don't understand how you can't have one.

 

[ianccy]

Oh, I got it, thank you Andrew, you are genius

 

[ianccy]

if the data in this sheet01 cell A1 is link to the data in sheet02 A1, what is the code?

the previous code works only when a value is enter, now the data is linked to other sheet, how to get it working?

 

[Andrew Poulsom]

Try this in the module for sheet02 (and delete the original code in sheet01):

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

 

[ianccy]

The code should be pasted to sheet 01?

and if my link is linked to sheet01 M1, what is the code?

thank you for the help

 

[Andrew Poulsom]

The code should be pasted to sheet 01?

and if my link is linked to sheet01 M1, what is the code?

thank you for the help

The code goes in the sheet that contains the changing cell (the one you want to use in the Query Connection string). You need to adjust "$A$1" and Range("A1") to refer the changing cell, and WorkSheets("sheet01") to refer to the sheet containing the QueryTable.