I'm looking for assistance coding a screen / web scrape utility in Excel 2010. The objective is to create a single table of data by:
1. Import data from a website with multiple pages.
2. Parse and clean data.
----------
GET DATA
----------
I recorded a macro for importing (see attached Book1.xlsm):
Sub Macro1()
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+Shift+M
'
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;https://www.ebuy.gsa.gov/advantage/s/search.do?q=19:0GS35F0451W&q=10:5CISCO+SYSTEMS&s=10&searchType=1&c=100&p=1" _
, Destination:=Range("$A$1"))
.Name = "5CISCO+SYSTEMS&s=10&searchType=1&c=100&p=1"
.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
End Sub
The code should repeat until all pages imported. It doesn't matter to me if all data is copied to a single worksheet or a separate one for each URL.
URL for items 1 - 100 is:
https://www.ebuy.gsa.gov/advantage/s/search.do?q=19:0GS35F0451W&q=10:5CISCO+SYSTEMS&s=10&searchType=1&c=100&p=1
URL for items 101-200 is:
https://www.ebuy.gsa.gov/advantage/s/search.do?q=19:0GS35F0451W&q=10:5CISCO+SYSTEMS&s=10&searchType=1&c=100&p=2
Continue until all items imported.
------
PARSE
------
I want to organize the data into a table with Part (text), Description (text), and Price (number w/ 2 decimal places).
See the "Parse" tab in the attached workbook, Book1.xlsm.
Thanks in advance for anyone willing to assist!
Bookmarks