DanOfEarth
06-16-2010, 06:35 AM
I'm trying to do a web data-scraper using the .QueryTables method.
I did a data "web query" and ended up with this code. I was going to scrape the data onto a blank sheet and work with it from there, however this thing is already giving me an error, and I CANNOT find anything on the net to help....
Sub Query3()
'
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://search.har.com/engine/doSearch.cfm?QUICKSEARCH=802%20Hallmark%20Oak" _
, Destination:=Range("Import!$A$1"))
.Name = "doSearch.cfm?QUICKSEARCH=802%20Hallmark%20Oak"
.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
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
If Range("A15") = "Search Result: (0) Records Found. " Then
Sheets("Leads").Range("H209").Formula = "N/A"
Sheets("Leads").Range("I209").Formula = "N/A"
End If
End Sub
It kicks it out on the first line (in red).
I've been playing with this thing for a month now. Any thoughts on this?
Thanks for listening...
I did a data "web query" and ended up with this code. I was going to scrape the data onto a blank sheet and work with it from there, however this thing is already giving me an error, and I CANNOT find anything on the net to help....
Sub Query3()
'
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://search.har.com/engine/doSearch.cfm?QUICKSEARCH=802%20Hallmark%20Oak" _
, Destination:=Range("Import!$A$1"))
.Name = "doSearch.cfm?QUICKSEARCH=802%20Hallmark%20Oak"
.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
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
If Range("A15") = "Search Result: (0) Records Found. " Then
Sheets("Leads").Range("H209").Formula = "N/A"
Sheets("Leads").Range("I209").Formula = "N/A"
End If
End Sub
It kicks it out on the first line (in red).
I've been playing with this thing for a month now. Any thoughts on this?
Thanks for listening...