PDA

View Full Version : VBA web queries - error 1004 "The site reports that the address is not valid"



fixcode
07-23-2011, 05:23 PM
I have attached my document. It is supposed to find the lowest price including shipping for an item. It should then do a web query for the underlying url under "Compare Prices", for the item in the list that has that as an option.

It worked to get the stuff from the list for about the first 5 items. I then hit the esc key, and now I get error 1004 "The site reports that the address is not valid." error message when I try to run the macro. Also originally I had commented out 2 of the web query enumerations so the link under "compare prices" would be accessible via a user-defined function. That presented the error message right away and didn't even work once.

How can I have the formatting appear in such a way that text links do work as hyperlinks, so the url under them is accessible various ways? At the very least I need the original web query set up in the document to run as-is without that error.

Thank you for your help.

fixcode
07-23-2011, 05:42 PM
Here is the code that should bring in the html from the site. It is causing error 1004.


Dim p As String
p = Application.WorksheetFunction.Substitute(Range("A" & b).Value, " ", "+")
www = "URL;" & "ht'''''I can't post link here according to forum rules''''/search?q=allintitle:" & p & "&num=100&hl=en&safe=active&tbs=p_ord:p,new:1&tbm=shop"
With ActiveSheet.QueryTables.Add(Connection:=www, Destination:=Range("D1"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With


What I really want is to see the hyperlinks. The code below works for other web queries on other sites, but produces the same error 1004 on google shopping. The only difference is there are 2 lines commented out below.


Dim p As String
p = Application.WorksheetFunction.Substitute(Range("A" & b).Value, " ", "+")
www = "URL;" & "ht'''''I can't post link here according to forum rules''''/search?q=allintitle:" & p & "&num=100&hl=en&safe=active&tbs=p_ord:p,new:1&tbm=shop"
With ActiveSheet.QueryTables.Add(Connection:=www, Destination:=Range("D1"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
'.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
'.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With