Copying Data From Website

[Sami2i]

I am needing to collect data off of a website. It is all in table format already and I have experimented with Data>Get External Data>From Web. This is ideal and works perfectly. However I need to get more information from it.

The URL are

http://example.com/representation/job-asstd?sort=coconst_order&start=1
http://example.com/representation/job-asstd?sort=coconst_order&start=50
http://example.com/representation/job-asstd?sort=coconst_order&start=100
http://example.com/representation/job-asstd?sort=coconst_order&start=150
http://example.com/representation/job-asstd?sort=coconst_order&start=200
and so on in multiples of 50.

The table is in the same place on every page.

How would I set this up to copy the table from these pages onto the next free row.

Thanks

Sam

 

[Spurious]

What I usually do, run a loop and create a new sheet for every download.
Then aggregate everything into one sheet at the end, given that the data is in the same place for every sheet, this will be an easy for each.

Example code:

Sub StartMultipleDownload()
Dim i As Long
Dim ws As Worksheet
i = 1
Do While i <= 201
    Set ws = Worksheets.Add
    Call DownloadWebquery(ws, i)
    i = i + 50
Loop
End Sub

Sub DownloadWebquery(ws As Worksheet, i As Long)
With ws.QueryTables.Add(Connection:= _
        "URL;http://example.com/representation/job-asstd?sort=coconst_order&start=" & i, Destination:=ws.Range("A1"))
        .Name = "example"
        .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

 

[Sami2i]

Spurious

I do not really know how to use macro, please can you quickly explain how to implement your code

Thanks

Sam

 

[Spurious]

Spurious

I do not really know how to use macro, please can you quickly explain how to implement your code

Thanks

Sam

Yes, sorry about that.

Do Alt + F11, the VBA Editor opens.
Then go to Insert --> Module.

On the left side, you see an explorer structure with modules.
Double click on the Module1 (should be the name of it).

Then copy and paste the code I posted above into it.

Now hit F5 and run the Macro "StartMultipleDownload".

At the moment the maximum of results are 250, change this portion to account for more:

Do While i <= [COLOR=#ff0000]201[/COLOR]

After running the code, you have a lot of worksheets with the data.
In order to aggregate them, you need to have another macro doing it.
Since I have no idea how the results look like in the Excel sheet, I cant help you there.

If you only got 250 results, copy and pasting the 5 sheets should do the trick.
You could use the macro recorder doing this, then you will have a macro (albeit badly constructed one) that will automate the process for you.