VBA Web Query

[Andy16H]

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.

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

 

[Andy16H]

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.

 

[Andy16H]

Bump