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

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,Tom Ogilvy"Amanda MMW" <Amanda
wrote in message ...
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

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




"Tom Ogilvy" wrote:

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,Tom Ogilvy"Amanda MMW" <Amanda
wrote in message ...
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

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

--
Regards,
Tom Ogilvy

"Amanda MMW" wrote in message
...
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




"Tom Ogilvy" wrote:

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,Tom Ogilvy"Amanda MMW" <Amanda
wrote in message
...
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