Error handling in loop

K

krice1974

Active Member
Joined
Jul 3, 2008
Messages
422
Hello excer'rs. I'm well through my current macro, but hung up on I guess what you'd call error handling. The code below loops through web addresses that contain a date 30x, downloading each days' data with a web query off of its web page. It seems that a couple things generate an error, server too busy possibly (and other error sources out of my control or knowledge) as well as no data/ page existing at all for some of the dates, like Sunday. I need to know how to not only resume execution when I hit an error but immediately jump to the incrementing of the loop counter to avoid running the loop's code that follows the web query part. Hope that makes sense. Any help anyone could offer would be greatly appreciated...

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
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 query
Dim EA As Worksheet             'Earnings worksheet
Dim i As Integer                'loop counter
Dim EAQFinalrow As Long         'The final row of the earings query sheet
Dim QT As QueryTable            'The query table
Dim EANewRow As Long            'The new row of the earnings main worksheet
Dim EarningsRangeSize As Long   'The size of the new data block (# of rows)
   
Set EAQ = Worksheets("EarningsQuery") 'Abbreviate/ set worksheet reference.
Set EA = Worksheets("Earnings")       'Abbreviate/ set worksheet reference.
For i = 1 To 30     'Loop counter, get each date and data with each pass.
    
    'Delete the old web query.
    EAQFinalrow = FinalRowFunct("EarningsQuery", 1)
    EAQ.Range(EAQ.Cells(1, 1), EAQ.Cells(EAQFinalrow, 1)).EntireRow.Delete
    
    'Format desired (needed for string address is Format("yyyymmdd")
    NextDay = Date + i
    'Yahoo address: [URL]http://biz.yahoo.com/research/earncal/20090320.html[/URL] (includes date of March 20, 2009)
    NextDayString = "[URL]http://biz.yahoo.com/research/earncal/[/URL]" & Format(NextDay, "yyyymmdd") & ".html"
    
    'Define a new web query
    Set QT = EAQ.QueryTables.Add(Connection:="URL;" & 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
    
    'Refresh the query
    QT.Refresh BackgroundQuery:=False
    
    'Find the final row of the query worksheet
    'EAQFinalrow = EAQ.Cells(Rows.count, 1).End(xlUp).Row
    EAQFinalrow = FinalRowFunct("EarningsQuery", 1)
    
    'Find the length of the data range
    EarningsRangeSize = EAQFinalrow - 4
    
    'Get the new row of the earnings main worksheet
    EANewRow = FinalRowFunct("Earnings", 3) + 1
    
    'Copy the company column to the earnings main worksheet
    EAQ.Range(EAQ.Cells(4, 1), EAQ.Cells(EAQFinalrow, 1)).Copy EA.Cells(EANewRow, 4)
    
    'Copy the symbol column to the earnings main worksheet
    EAQ.Range(EAQ.Cells(4, 2), EAQ.Cells(EAQFinalrow, 2)).Copy EA.Cells(EANewRow, 3)
    
    'Copy the time column to the earnings main worksheet
    EAQ.Range(EAQ.Cells(4, 4), EAQ.Cells(EAQFinalrow, 4)).Copy EA.Cells(EANewRow, 7)
    
    'Enter the earnings date column to the earnings main worksheet
    EA.Range(EA.Cells(EANewRow, 6), EA.Cells(EANewRow + EarningsRangeSize, 6)).Value = Format(NextDay, "mm/dd/yyyy")
    
    'Call sub. Enter "NA" for the 3rd, 4th and 5th criteria columns
    AddNA "Earnings", 3, 8, 10
    
Next i
End Sub
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
OK. I just added my first ever attempt at error handling, I used an on error goto next i(skip current block and continue with the loop). *Code below.* It worked to get me past the first error, but is thrown again just a few passes later. Argh. Any ideas?

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
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 query
Dim EA As Worksheet             'Earnings worksheet
Dim i As Integer                'loop counter
Dim EAQFinalrow As Long         'The final row of the earings query sheet
Dim QT As QueryTable            'The query table
Dim EANewRow As Long            'The new row of the earnings main worksheet
Dim EarningsRangeSize As Long   'The size of the new data block (# of rows)
   
Set EAQ = Worksheets("EarningsQuery") 'Abbreviate/ set worksheet reference.
Set EA = Worksheets("Earnings")       'Abbreviate/ set worksheet reference.
For i = 1 To 30     'Loop counter, get each date and data with each pass.
    
    'Delete the old web query.
    EAQFinalrow = FinalRowFunct("EarningsQuery", 1)
    EAQ.Range(EAQ.Cells(1, 1), EAQ.Cells(EAQFinalrow, 1)).EntireRow.Delete
    
    'Format desired (needed for string address is Format("yyyymmdd")
    NextDay = Date + i
    'Yahoo address: [URL]http://biz.yahoo.com/research/earncal/20090320.html[/URL] (includes date of March 20, 2009)
    NextDayString = "[URL]http://biz.yahoo.com/research/earncal/[/URL]" & Format(NextDay, "yyyymmdd") & ".html"
    
    'Error handling
    On Error GoTo Increment
    
    'Define a new web query
    Set QT = EAQ.QueryTables.Add(Connection:="URL;" & 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
    
    'Refresh the query
    QT.Refresh BackgroundQuery:=False
    
    'Find the final row of the query worksheet
    'EAQFinalrow = EAQ.Cells(Rows.count, 1).End(xlUp).Row
    EAQFinalrow = FinalRowFunct("EarningsQuery", 1)
    
    'Find the length of the data range
    EarningsRangeSize = EAQFinalrow - 4
    
    'Get the new row of the earnings main worksheet
    EANewRow = FinalRowFunct("Earnings", 3) + 1
    
    'Copy the company column to the earnings main worksheet
    EAQ.Range(EAQ.Cells(4, 1), EAQ.Cells(EAQFinalrow, 1)).Copy EA.Cells(EANewRow, 4)
    
    'Copy the symbol column to the earnings main worksheet
    EAQ.Range(EAQ.Cells(4, 2), EAQ.Cells(EAQFinalrow, 2)).Copy EA.Cells(EANewRow, 3)
    
    'Copy the time column to the earnings main worksheet
    EAQ.Range(EAQ.Cells(4, 4), EAQ.Cells(EAQFinalrow, 4)).Copy EA.Cells(EANewRow, 7)
    
    'Enter the earnings date column to the earnings main worksheet
    EA.Range(EA.Cells(EANewRow, 6), EA.Cells(EANewRow + EarningsRangeSize, 6)).Value = Format(NextDay, "mm/dd/yyyy")
    
    'Call sub. Enter "NA" for the 3rd, 4th and 5th criteria columns
    AddNA "Earnings", 3, 8, 10
    
Increment:
Next i
End Sub
 
Upvote 0
I see just above your last 3 lines the line Increment:
But you have not provided any code to run AFTER the Increment: line is processed
 
Upvote 0
Thanks Jim. I know I'm a little wet behind the ears so please bear with me here... doesn't "next i" execute immediately after an error then?
 
Upvote 0
I'm a bit of a "light-weight" when it comes to error handling myself - so I better hod off of contributing any bad-info -- I'de like to stay tuned and watch when someone gives you the right answer. afterwards, I will have a worth while contribution.
Jim
 
Upvote 0
I think lightweight is a relative term... next to me, you're a pro i'm sure. I'd be grateful for anything you can offer, thanks in any case.
 
Upvote 0
Until someone comes along with the exact answer for you, here goes some background that I've acquired (right ot wrong)..

1) Error handling is especially important and appropriate when code is susceptible to errors. Just looking at code and "seeing" that a particular line might cause an error is to me the big experience thing (lacking, for now)

2) the line OnError Resume Next 'line allows the code to run (skipping over any error, but processing what it understand to the end) without the R/T error box poping Up with the Alternatives End or Debug

3) The OnError Goto should be placed early in the code; Once you have passed the code are where an error in less likely you should add the line On error Goto 0 to reinstate excel's job of looking for any other potential errors.

4) If teh line On Error Goto errHandler line is run - the code obviouly jumps to the
errHandler: line to run whatever code follows. Usually ONE LINE ABOVE THE errHandler
line you enter the line Exit Sub so that if the OnError Goto errhandler line above IS NOT run then You will Exit the Code BEFORE coming to the errHandler: Line (and subsequent lines will get run))...

I'll stop for now...
HTH
 
Upvote 0
Thanks for the quick and dirty, I appreciate the effort. I think I get the absolute basics, I just can't figure out the structure to jump to the counter increment line (stay in the loop while handling the error) I guess is my problem. On error resume next would execute code that I wish to skip if there is an error (the entire pass through the loop is invalid) Thanks though, maybe someone has a specific solution.
 
Upvote 0
Success! Thanks Jim. A resume/ line label did the trick. The working code is below if you're curious. If anyone has any suggestions for the sake of standards or efficiency, please let me know...

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

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 query
Dim EA As Worksheet             'Earnings worksheet
Dim i As Integer                'loop counter
Dim EAQFinalrow As Long         'The final row of the earings query sheet
Dim QT As QueryTable            'The query table
Dim EANewRow As Long            'The new row of the earnings main worksheet
Dim EarningsRangeSize As Long   'The size of the new data block (# of rows)
   
Set EAQ = Worksheets("EarningsQuery") 'Abbreviate/ set worksheet reference.
Set EA = Worksheets("Earnings")       'Abbreviate/ set worksheet reference.

For i = 1 To 30     'Loop counter, get each date and data with each pass.
    
    'Delete the old web query.
    EAQFinalrow = FinalRowFunct("EarningsQuery", 1)
    EAQ.Range(EAQ.Cells(1, 1), EAQ.Cells(EAQFinalrow, 1)).EntireRow.Delete
    
    '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"
    
    'Error handling
    On Error GoTo Increment
    
    'Define a new web query
    Set QT = EAQ.QueryTables.Add(Connection:="URL;" & 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
    
    'Refresh the query
    QT.Refresh BackgroundQuery:=False
    
    'Find the final row of the query worksheet
    'EAQFinalrow = EAQ.Cells(Rows.count, 1).End(xlUp).Row
    EAQFinalrow = FinalRowFunct("EarningsQuery", 1)
    
    'Find the length of the data range
    EarningsRangeSize = EAQFinalrow - 4
    
    'Get the new row of the earnings main worksheet
    EANewRow = FinalRowFunct("Earnings", 3) + 1
    
    'Copy the company column to the earnings main worksheet
    EAQ.Range(EAQ.Cells(4, 1), EAQ.Cells(EAQFinalrow, 1)).Copy EA.Cells(EANewRow, 4)
    
    'Copy the symbol column to the earnings main worksheet
    EAQ.Range(EAQ.Cells(4, 2), EAQ.Cells(EAQFinalrow, 2)).Copy EA.Cells(EANewRow, 3)
    
    'Copy the time column to the earnings main worksheet
    EAQ.Range(EAQ.Cells(4, 4), EAQ.Cells(EAQFinalrow, 4)).Copy EA.Cells(EANewRow, 7)
    
    'Enter the earnings date column to the earnings main worksheet
    EA.Range(EA.Cells(EANewRow, 6), EA.Cells(EANewRow + EarningsRangeSize, 6)).Value = Format(NextDay, "mm/dd/yyyy")
    
    'Call sub. Enter "NA" for the 3rd, 4th and 5th criteria columns
    AddNA "Earnings", 3, 8, 10
    
Label1:
    Next i
    
MsgBox "Done"
Exit Sub

Increment:
    Resume Label1

End Sub
 
Upvote 0
Hello,

i´m trying to download earningsdates from yahoo. I found this post with this macro but just using the macro doenst transfer any content to my excel template.
I´m absolutely new to macros/<acronym title="visual basic for applications">vba</acronym>. Do you might have a template where i just have to hit the button or a hint, how i can transfer content successfull to my excel?
Do i might need some content as a source before i can start the macro doing anything?


Thank you in Advance
Trando
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Power Query - Data Refresh Error in Macro
Replies
3
Views
133
bobsan42
bobsan42
J
  • Question
VBA Script - Can't figure out one error...
2
Replies
15
Views
232
Peter_SSs
Peter_SSs
J
  • Question
VBA Script to Update Files in a folder, updating Links
Replies
1
Views
107
Micron
M
N
  • Solved
Sort emails in Outlook 365 folder, and loop through them opening the oldest first
Replies
7
Views
763
John_w
J
N
  • Solved
Loop to get emails from Outlook 365 has broken, and I cant work out why !
Replies
3
Views
501
netrixuser
N

Forum statistics

Threads
1,214,848
Messages
6,121,914
Members
449,054
Latest member
luca142

Share this page

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
Back
Top