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.
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