Editing an External Web Query Import

[Orisopha]

Hi,

I've currently got an Excel sheet with a table I've imported from a
web page I've created. This was done using the Data->Import External
Data->New Web Query. The page itself is a CGI script and as such
takes arguments in the address.

I was wondering if anyone knows how I can alter that URL from a cell
in Excel. Specifically I want to be able to change the hostname in a
cell in Excel and the corresponding ?hostname='blah' to change.

Any ideas would be greatly appreciated.

Jim

 

[jaf]

Data>get external data>run saved query. Right click the query and select
edit.

 

[Orisopha]

Unforturnately, I wanted to be able to edit the url from a cell. I.e.
To enter the hostname in a cell and for that to be set in the url.

 

[jaf]

Record a macro while running the query. Then edit the macro to take cell
values.

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 7/18/2003 by jaf
'

'
myurl = "URL;http://" & Sheet6.Cells(1, 15).Text '<you could set up a loop
to use a range of cells.


With ActiveSheet.QueryTables.Add(Connection:=myurl,
Destination:=Range("A1"))
.Name = "pp"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
End Sub