Connection string for query table in loop error

[krice1974]

Hi all. I'm working my way through a macro that increments a date variable in a loop, adding a day with each pass. I then pass the new date into a string that is the connection string for a web query. I've done this before with success, but this one is giving me "application/ object defined error" in the line "Set QT= "

Something I'm missing today? Thanks in advance...

Sub EarningsMacro()

'The sub that downloads earnings dates.
'Looks out one month ahead.
'Dates can be added to the data on the website within a month, so it has to check for the whole month every day.
'Need variables TodayMonth, TodayDay, TodayYear, CheckMonth, CheckDay, CheckYear

'Get the current date
'Get the next day =(function NextDay to set variables TodayMonth, TodayDay, TodayYear)
           
'Start web query
    'for i= 1 to 30
    'set up string for web query address with NextDay in this format: March 13, 2009= 20090313
    'set up web query
    'copy the data to the main earnings worksheet
    'delete the web query
    'next i
    
Dim NextDay As Date         'The variable that holds the date being checked
Dim NextDayString As String 'The web address that holds the variable to change the date. Passed to the query in the loop.
Dim EAQ As Worksheet         'Earnings worksheet
Dim i As Integer            'loop counter
Dim QT As QueryTable        'The query table
   
Set EAQ = Worksheets("EarningsQuery") 'Abbreviate/ set worksheet reference.

For i = 1 To 30     'Loop counter, get each date and data with each pass.
    'Format desired (needed for string address is Format("yyyymmdd")
    NextDay = Date + i
    'Yahoo address: http://biz.yahoo.com/research/earncal/20090320.html (includes date of March 20, 2009)
    NextDayString = "http://biz.yahoo.com/research/earncal/" & Format(NextDay, "yyyymmdd") & ".html"
    
    'Define a new web query
    
    Set QT = EAQ.QueryTables.Add(Connection:=NextDayString, Destination:=EAQ.Range("$A$1"))
    
    With QT
        .Name = "20090320"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "5"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With

 

[krice1974]

I know it's probably just a reference off, but I still haven't gotten it. *help please, I know I'm in the right place! :P

 

[John_w]

Try:

    Set qt = EAQ.QueryTables.Add(Connection:="URL;" & NextDayString, Destination:=EAQ.Range("$A$1"))

http://biz.yahoo.com/research/earncal/20090329.html returns the same error 1004 though, maybe because it's a Sunday. Either don't request Sundays or handle the error.

 

[krice1974]

Twice in one day, John, that's worthy of 100 thanks...

 

[Richard Schollar]

If you want a way to check if a file exists at a given URL then check out Juan Pablo's function in this thread:

http://www.mrexcel.com/forum/showthread.php?t=265520

 

[krice1974]

Thank you! Very useful.