VBA Web Query

Andy16H

Board Regular
Joined
Apr 17, 2010
Messages
192
I'm trying to download several hundred tables from a website. The address to the pages has two variables, Date and page number. I have created a worksheet that has every address for all the dates without the page #. THe address start in cell A1 on worksheet SiteAddress. THe tables will be posted in a worksheet titled Trades.

The problem i'm having is some of the dates have as many as 10 pages and some only have 1 page. Lets assume that 8/15/2005 only has 1 page. When it goes to search for page 2 it will bring back and post in the last row in column A NO TRADES. When the code sees NO TRADES in the last row I need it to go to the Next i, if NO TRADES is not in the last row then I need it to go to Next p.
Code:
Sub InsiderTradesQuery()

Dim myQueryTable As QueryTable
Dim ConnectString As String
Dim LastRow As Long
Dim LastRowDel As Long
Dim LastRowDelValue As String
Dim LastAddress As Long
Dim SiteAddress As Worksheet
Dim Trades As Worksheet
Dim Address1 As Range
Dim i As Integer
Dim p As Integer


Set SiteAddress = Application.Worksheets("SiteAddress")
Set Trades = Application.Worksheets("Trades")


LastAddress = SiteAddress.Cells(Rows.Count, 1).End(xlUp).Row
LastRowDel = Trades.Range("A" & Trades.Rows.Count).End(xlUp).Row
LastRowDelValue = Trades.Range("A" & LastRowDel).Value




For i = 1 To LastAddress
    For p = 1 To 10
         If LastRowDelValue = "" Then
        ConnectString = "URL;" & SiteAddress.Cells(i, 1).Value & p
        ElseIf LastRowDelValue = "NO RESULTS" Then
        GoTo SkipCode:
        End If


For Each myQueryTable In Trades.QueryTables
    myQueryTable.Delete
Next myQueryTable


LastRow = Trades.Range("A" & Trades.Rows.Count).End(xlUp).Row + 1
Set Address1 = Trades.Range("A1")
If Address1 = "" Then
Set Address1 = Trades.Range("A1")
Else:
Set Address1 = Trades.Range("A" & LastRow)
End If


Set myQueryTable = Trades.QueryTables.Add(Connection:=ConnectString, _
    Destination:=Address1)
  
With myQueryTable
    .Name = "TradingDB"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlSpecifiedTables
    .WebFormatting = xlWebFormattingNone
    .WebTables = "tracker"
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
End With


myQueryTable.Refresh BackgroundQuery:=False


LastRowDel = Trades.Range("A" & Trades.Rows.Count).End(xlUp).Row
LastRowDelValue = Trades.Range("A" & LastRowDel).Value


Next p


SkipCode:
Next i


End Sub
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Andy16H

Board Regular
Joined
Apr 17, 2010
Messages
192
I'm continuing to search and try different things and I'm still having no luck. There are 2 For loops and depending on the results that are returned to the work sheet I need 1 of the for loops to proceed.
The for loop should always go to Next P unless the last row = "NO RESULTS" if last row = "NO RESULTS" the the for Loop needs to go to Next i.

Any help is greatly appreciated.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,556
Messages
5,596,820
Members
414,104
Latest member
imamalidadashzada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top