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

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You can use the HYPERLINK function like this:

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

ianccy

Active Member
Joined
Jul 28, 2002
Messages
332
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Like this?

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

ianccy

Active Member
Joined
Jul 28, 2002
Messages
332

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,048
Office Version
  1. 365
Platform
  1. Windows
ianccy

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

ianccy

Active Member
Joined
Jul 28, 2002
Messages
332

ADVERTISEMENT

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

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,048
Office Version
  1. 365
Platform
  1. Windows
I just recorded this.

Code:
    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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
ianccy said:
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:

Code:
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

Active Member
Joined
Jul 28, 2002
Messages
332
With ActiveSheet.QueryTables(1)

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

Watch MrExcel Video

Forum statistics

Threads
1,118,234
Messages
5,571,045
Members
412,359
Latest member
misstoffeepenny
Top