Dynamic Web Query

A

Amanda MMW

I am trying to use excel 2003 to process online orders with a macro but am having problems with the Web Query function as follows

Cell A1 = Order number - say "1273976"

Cell A2 use excel functions "concantenate" and "hyperlink" to create the hyperlink from this order number - say http://www.anyname.com/1273976.htm

I would like cell A3 to do a Web Query based on the hyperlink in Cell A2

I have been unable to create this either with macro's or by trying to get the web query function to use the URL found in cell A2

Any ideas gratefully appreciated !!!

Thanks
 
T

Tom Ogilvy

Cells/formulas don't do web queries with any built in functions. Another
question would be when should the query be performed? Should it be done on
each calculate? Should the user click a button? As far as code to do it,
turn on the macro recorder and perform the web query manually. Then in the
recorded string, replace the hard coded URL with a variable (using
concatenation). Then figure out how you want to trigger it.

the sample code for the Webtables property of the querytable object shows
this:

Set shFirstQtr = Workbooks(1).Worksheets(1)
Set qtQtrResults = shFirstQtr.QueryTables _
.Add(Connection := "URL;http://datasvr/98q1/19980331.htm", _
Destination := shFirstQtr.Cells(1,1))
With qtQtrResults
.WebFormatting = xlNone
.WebSelectionType = xlSpecifiedTables
.WebTables = "1,2"
.Refresh
End With

So you would modify such code like

Dim sh as Worksheet
sh = worksheets("Sheet1")
Set shFirstQtr = Workbooks(1).Worksheets(1)
Set qtQtrResults = shFirstQtr.QueryTables _
.Add(Connection := "URL;" & sh.Range("A2").Value, _
Destination := shFirstQtr.Cells(1,1))
With qtQtrResults
.WebFormatting = xlNone
.WebSelectionType = xlSpecifiedTables
.WebTables = "1,2"
.Refresh
End With



as an example.
-- Regards said:
I am trying to use excel 2003 to process online orders with a macro but am
Click to expand...
having problems with the Web Query function as follows
Cell A1 = Order number - say "1273976"

Cell A2 use excel functions "concantenate" and "hyperlink" to create the
Click to expand...
hyperlink from this order number - say http://www.anyname.com/1273976.htm
I would like cell A3 to do a Web Query based on the hyperlink in Cell A2

I have been unable to create this either with macro's or by trying to get
Click to expand...
the web query function to use the URL found in cell A2
 
A

Amanda MMW

Hi Tom

Many thanks for the suggestion - unfortunately this produces syntax error: "object required"

herewith the macro originally recorded with a static URL:

Sub testerRefresh()
'
' testerRefresh Macro
' Macro recorded 6/12/2004 by AA
'

'
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;https://anydomain.com/servlet/OrderUpdate?orderid=15360988", _
Destination:=Range("A3"))
.Name = "OrderUpdate"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingRTF
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=True
End With
End Sub

Replacing in this macro the following lines:

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;https://anydomain.com/servlet/OrderUpdate?orderid=15360988", _

with your suggestion to obtain the URL value from the active worksheet cell A2:

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & sh.Range("A2").Value, _

produces "syntax error" "object required"

In answer to your questions regarding the
"when should the query be performed? Should it be done on
each calculate? Should the user click a button?"
- the query should be performed each time the macro is run

Any ideas??

Thanks
 
T

Tom Ogilvy

Assuming that since it is a URL, that excel turns it into a hyperlink, this
worked for me:

Sub Tester1()
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & Range("C1").Hyperlinks(1).Address, _
Destination:=Range("A3"))
.Name = "OrderUpdate"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingRTF
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
' .WebDisableRedirections = False
.Refresh BackgroundQuery:=True
End With
End Sub
 
Top