VBA to web scrape multiple urls

[Jamie]

Does anyone have a macro that will search and scrape data from multiple links, provided the links are somewhat similar? ie.http://www.esind.com/pages/products/products_01_1.tpl is an example page. The only variation is the underscores/numbers. So another product page ishttp://www.esind.com/pages/products/products_01_2.tpl. And so on.

Thanks everyone!

 

[Luke M]

Hi Jamie,

I tried to do something similar once on the forums here, scraping the member pages.
http://chandoo.org/forum/threads/chandoo-member-list.16003/

Maybe it will give you some ideas?

 

[Jamie]

That is so cool, but completely over my head. I couldn't even get past step one, references. Anyway we can do without? I have a similar code that uses a different concept (page number) I was trying to modify for my needs, but I haven't gotten anywhere with this either. I've attached a file with it.

 

[Jamie]

Sub Macro1()
'
' Macro1 Macro
'
'
  Sheets("Sheet1").Select
  Range("A1").Select
Dim i As Integer, myurl As String
i = 1
Do While i < 300000
myurl = "URL;http://catalog.bd.com/nexus-ecat/getProductDetail?productId=" & i & ""
 
 
 
  With ActiveSheet.QueryTables.Add(Connection:=myurl, Destination:=Range("$A$1"))
  .Name = shorturl
  .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
  .WebDisableDateRecognition = False
  .WebDisableRedirections = False
  .Refresh BackgroundQuery:=False
  End With
 
  Columns("A:J").Select
  Selection.Copy
  Range("K1").Select
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  :=False, Transpose:=False
  Columns("A:J").Select
  Range("J1").Activate
  Application.CutCopyMode = False
  Selection.Delete Shift:=xlToLeft
  Columns("A:J").Select
  Selection.ColumnWidth = 20.01
  Columns("B:B").Select
  Selection.ColumnWidth = 20.01
  Rows("1:300").Select
  Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
 
  i = i + 1
 
Loop
 
 
End Sub

Here's what I got so far, and it works ok. How do I get it to only pull data from valid url strings ending between 200000-300000? Any advice, as always, is greatly appreciated.

 

[Luke M]

For that large of numbers, don't Dim I as an Integer, which has a upper limit of about 32000. Define it as a Long.

Dim i as Long

That should solve the issue with big numbers.