Problem with the Web Query
 

I have certain urls which gives current stock price for that perticuler
stock. I created web query to get those results from web to excel
sheet. Althougt the url is currect and works when pasted on browsers
url box, it fails in the vba code. The error is run time error 1004.
The address of the site is not valid. Check the address and try again.
VBA code fails on line .Refresh BackgroundQuery:=False as indicated
below.
Please note that if I run individual web querry with the same hardcoded
url, it works.
Request some light on this.
Regards,

My code as below.

Sub GET_PRICE_ALL()
R = 1
Sheets("MYLINKS").Select
Sheets("MYLINKS").Range("A" & R).Activate

Do Until IsEmpty(ActiveCell)
MYURL = ActiveCell.Value
GET_PRICE (MYURL)
Sheets("MYLINKS").Activate
ActiveCell.Offset(1, 0).Select
R = R + 1
Loop
End Sub
Sub GET_PRICE(MYURL As String)
Sheets("RESULT").Select
Sheets("RESULT").Range("A" & R + 5).Activate
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;MYURL" _
, Destination:=Sheets("RESULT").Range("A" & R + 5))
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingAll
.WebTables = "10"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False <<===here vba code fails.
End With

End Sub

Problem with the Web Query
 

"URL;MYURL" _

should be

"URL;" & MYURL _

otherwise it is trying to go to the literal string MYURL which is not what
you are trying to do.

--
Regards,
Tom Ogilvy

"Shetty" wrote in message
oups.com...
I have certain urls which gives current stock price for that perticuler
stock. I created web query to get those results from web to excel
sheet. Althougt the url is currect and works when pasted on browsers
url box, it fails in the vba code. The error is run time error 1004.
The address of the site is not valid. Check the address and try again.
VBA code fails on line .Refresh BackgroundQuery:=False as indicated
below.
Please note that if I run individual web querry with the same hardcoded
url, it works.
Request some light on this.
Regards,

My code as below.

Sub GET_PRICE_ALL()
R = 1
Sheets("MYLINKS").Select
Sheets("MYLINKS").Range("A" & R).Activate

Do Until IsEmpty(ActiveCell)
MYURL = ActiveCell.Value
GET_PRICE (MYURL)
Sheets("MYLINKS").Activate
ActiveCell.Offset(1, 0).Select
R = R + 1
Loop
End Sub
Sub GET_PRICE(MYURL As String)
Sheets("RESULT").Select
Sheets("RESULT").Range("A" & R + 5).Activate
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;MYURL" _
, Destination:=Sheets("RESULT").Range("A" & R + 5))
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingAll
.WebTables = "10"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False <<===here vba code fails.
End With

End Sub

Problem with the Web Query
 

Tom,
Thank you. A very small error became very big for me.
Now another problem poped up.
After getting these data, I have to get some additional data.
On actual web page I have to click the link which fires up a new
window.
Here, I failed. This new window has some connection with the old window
and shows additional data for the company in the old window. I have
gone thru all the html code but could not figure out, how to construct
the url. Web quary need to pick up one table from new window.
Is there a menhod by which I can first nevigate to the advanced
page(new window) and from the new page I can instruct the excel to pick
one table?

I hope there is a way.
Thanks again for your help.
Regards,

Tom Ogilvy wrote:
"URL;MYURL" _

should be

"URL;" & MYURL _

otherwise it is trying to go to the literal string MYURL which is not what
you are trying to do.

--
Regards,
Tom Ogilvy

"Shetty" wrote in message
oups.com...
I have certain urls which gives current stock price for that perticuler
stock. I created web query to get those results from web to excel
sheet. Althougt the url is currect and works when pasted on browsers
url box, it fails in the vba code. The error is run time error 1004.
The address of the site is not valid. Check the address and try again.
VBA code fails on line .Refresh BackgroundQuery:=False as indicated
below.
Please note that if I run individual web querry with the same hardcoded
url, it works.
Request some light on this.
Regards,

My code as below.

Sub GET_PRICE_ALL()
R = 1
Sheets("MYLINKS").Select
Sheets("MYLINKS").Range("A" & R).Activate

Do Until IsEmpty(ActiveCell)
MYURL = ActiveCell.Value
GET_PRICE (MYURL)
Sheets("MYLINKS").Activate
ActiveCell.Offset(1, 0).Select
R = R + 1
Loop
End Sub
Sub GET_PRICE(MYURL As String)
Sheets("RESULT").Select
Sheets("RESULT").Range("A" & R + 5).Activate
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;MYURL" _
, Destination:=Sheets("RESULT").Range("A" & R + 5))
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingAll
.WebTables = "10"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False <<===here vba code fails.
End With

End Sub