Connection string for query table in loop error

krice1974

Active Member
Joined
Jul 3, 2008
Messages
422
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...

Code:
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
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

krice1974

Active Member
Joined
Jul 3, 2008
Messages
422
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,514
Messages
5,602,093
Members
414,501
Latest member
mdhaumyu

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