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

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

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,218
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,218
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,122,827
Messages
5,598,331
Members
414,231
Latest member
Mig

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top