Can run-time 1004 be trapped ?
on error goto errhandler
errhandler: if err.Number = 1004 then end if Resume Next End Sub -- Regards, Tom Ogilvy "BHARATH RAJAMANI" wrote in message ... Does anyone have code to trap run-time error '1004' ? I'd like to trap '1004' in a macro that reads (case-sensitive) csv filenames from a (derivatives exchange) website. TIA !!! Regards, BR -- Manager, International Private Banking, International Banking Group, ICICI Bank East Wing 8th floor South, ICICI Towers, Bandra Kurla Complex, Mumbai India 400051 |
Can run-time 1004 be trapped ?
thanks Tom !! Can error codes beyond 1000 be trapped? Do I have to clear the
Err. object at start of subroutine? My code is as below, however the 1004 run-time error seems to throw up before I can catch it !! Pls advise. Try this macro from 2 OCT 2003 to 31 OCT 2003 as input parameters. (The markets were closed on 2 Oct 2003, so I want to move on to the next trading day & retrieve the EOD file) 'My macro starts here Option Explicit Sub get_all_bhavcopy_From_nse_website() Dim CTR1 As Date Dim START_DT As Date Dim END_DT As Date Dim URL As String Dim ROWCOUNT As Integer Dim BHAVCOPY As Workbook Dim ERRBOOK As Workbook Dim ERRSHEET As Worksheet Dim FILENAME As String Set ERRBOOK = Workbooks.Open("D:\INVESTMENTS\ERRORS.XLS") Set ERRSHEET = ERRBOOK.Worksheets(1) ERRSHEET.Cells(1, 1).Value = "NO BHAVCOPY FOR THESE DAYS" ROWCOUNT = 2 START_DT = InputBox("START FROM DATE:") END_DT = InputBox("END ON DATE:") For CTR1 = START_DT To END_DT If (Weekday(CTR1) = 2 And Weekday(CTR1) <= 6) Then URL = CStr("http://www.nseindia.com/content/historical/DERIVATIVES/" & CStr(Year(CTR1)) & "/" & UCase(CStr(Format(CTR1, "MMM"))) & "/fo" & CStr(Format(CTR1, "D")) & UCase(CStr(Format(CTR1, "MMM"))) & CStr(Format(CTR1, "YYYY")) & "bhav.csv") Set BHAVCOPY = Workbooks.Open(URL) On Error GoTo errhandler With BHAVCOPY ..SaveAs FILENAME:=CStr("D:\INVESTMENTS\CSV\fo" & CStr(Format(CTR1, "D")) & UCase(CStr(Format(CTR1, "MMM"))) & CStr(Format(CTR1, "YYYY")) & "bhav.csv"), _ FileFormat:=xlCSV ..Close SAVECHANGES:=False End With End If NEXTDAY: Next CTR1 With ERRBOOK ..Close SAVECHANGES:=True End With Exit Sub errhandler: If Err.Number = 1004 Then ROWCOUNT = ROWCOUNT + 1 ERRSHEET.Cells(ROWCOUNT, 1).Value = URL 'MsgBox "CANNOT FIND " & URL End If Resume Next End Sub 'My macro ends above "Tom Ogilvy" wrote: on error goto errhandler errhandler: if err.Number = 1004 then end if Resume Next End Sub -- Regards, Tom Ogilvy "BHARATH RAJAMANI" wrote in message ... Does anyone have code to trap run-time error '1004' ? I'd like to trap '1004' in a macro that reads (case-sensitive) csv filenames from a (derivatives exchange) website. TIA !!! Regards, BR -- Manager, International Private Banking, International Banking Group, ICICI Bank East Wing 8th floor South, ICICI Towers, Bandra Kurla Complex, Mumbai India 400051 |
Can run-time 1004 be trapped ?
THX Tom !! it works :-) .... I appended an Exit Sub before the Errhandler.
"Tom Ogilvy" wrote: on error goto errhandler errhandler: if err.Number = 1004 then end if Resume Next End Sub -- Regards, Tom Ogilvy "BHARATH RAJAMANI" wrote in message ... Does anyone have code to trap run-time error '1004' ? I'd like to trap '1004' in a macro that reads (case-sensitive) csv filenames from a (derivatives exchange) website. TIA !!! Regards, BR -- Manager, International Private Banking, International Banking Group, ICICI Bank East Wing 8th floor South, ICICI Towers, Bandra Kurla Complex, Mumbai India 400051 |
Can run-time 1004 be trapped ?
all trappable errors would be sent to the error handler. You can use a case
statement to react to certain ones Case Err.Number Case 1004 Case 438 end Select Case Resume Next The resume next clears the error and tells excel it is no longer in error handling mode. The statement doesn't have to be resume next, but it has to be one of the resume statements. (See help on resume for details). 'My macro starts here Option Explicit Sub get_all_bhavcopy_From_nse_website() Dim CTR1 As Date Dim START_DT As Date Dim END_DT As Date Dim URL As String Dim ROWCOUNT As Integer Dim BHAVCOPY As Workbook Dim ERRBOOK As Workbook Dim ERRSHEET As Worksheet Dim FILENAME As String Set ERRBOOK = Workbooks.Open("D:\INVESTMENTS\ERRORS.XLS") Set ERRSHEET = ERRBOOK.Worksheets(1) ERRSHEET.Cells(1, 1).Value = "NO BHAVCOPY FOR THESE DAYS" ROWCOUNT = 2 START_DT = InputBox("START FROM DATE:") END_DT = InputBox("END ON DATE:") For CTR1 = START_DT To END_DT If (Weekday(CTR1) = 2 And Weekday(CTR1) <= 6) Then URL = CStr("http://www.nseindia.com/content/historical/DERIVATIVES/" & CStr(Year(CTR1)) & "/" & UCase(CStr(Format(CTR1, "MMM"))) & "/fo" & CStr(Format(CTR1, "D")) & UCase(CStr(Format(CTR1, "MMM"))) & CStr(Format(CTR1, "YYYY")) & "bhav.csv") On Error GoTo errhandler ' move above the possible cause of the error Set BHAVCOPY = Workbooks.Open(URL) On Error goto 0 ' back to default error handling With BHAVCOPY ..SaveAs FILENAME:=CStr("D:\INVESTMENTS\CSV\fo" & CStr(Format(CTR1, "D")) & UCase(CStr(Format(CTR1, "MMM"))) & CStr(Format(CTR1, "YYYY")) & "bhav.csv"), _ FileFormat:=xlCSV ..Close SAVECHANGES:=False End With End If NEXTDAY: Next CTR1 With ERRBOOK ..Close SAVECHANGES:=True End With Exit Sub errhandler: If Err.Number = 1004 Then ROWCOUNT = ROWCOUNT + 1 ERRSHEET.Cells(ROWCOUNT, 1).Value = URL 'MsgBox "CANNOT FIND " & URL End If Resume Next End Sub I set the error handling back to the default (unhandled) after the URL is opened. If you want different behavior, then move the commands where appropriate. -- Regards, Tom Ogilvy "BHARATH RAJAMANI" wrote in message ... thanks Tom !! Can error codes beyond 1000 be trapped? Do I have to clear the Err. object at start of subroutine? My code is as below, however the 1004 run-time error seems to throw up before I can catch it !! Pls advise. Try this macro from 2 OCT 2003 to 31 OCT 2003 as input parameters. (The markets were closed on 2 Oct 2003, so I want to move on to the next trading day & retrieve the EOD file) 'My macro starts here Option Explicit Sub get_all_bhavcopy_From_nse_website() Dim CTR1 As Date Dim START_DT As Date Dim END_DT As Date Dim URL As String Dim ROWCOUNT As Integer Dim BHAVCOPY As Workbook Dim ERRBOOK As Workbook Dim ERRSHEET As Worksheet Dim FILENAME As String Set ERRBOOK = Workbooks.Open("D:\INVESTMENTS\ERRORS.XLS") Set ERRSHEET = ERRBOOK.Worksheets(1) ERRSHEET.Cells(1, 1).Value = "NO BHAVCOPY FOR THESE DAYS" ROWCOUNT = 2 START_DT = InputBox("START FROM DATE:") END_DT = InputBox("END ON DATE:") For CTR1 = START_DT To END_DT If (Weekday(CTR1) = 2 And Weekday(CTR1) <= 6) Then URL = CStr("http://www.nseindia.com/content/historical/DERIVATIVES/" & CStr(Year(CTR1)) & "/" & UCase(CStr(Format(CTR1, "MMM"))) & "/fo" & CStr(Format(CTR1, "D")) & UCase(CStr(Format(CTR1, "MMM"))) & CStr(Format(CTR1, "YYYY")) & "bhav.csv") Set BHAVCOPY = Workbooks.Open(URL) On Error GoTo errhandler With BHAVCOPY .SaveAs FILENAME:=CStr("D:\INVESTMENTS\CSV\fo" & CStr(Format(CTR1, "D")) & UCase(CStr(Format(CTR1, "MMM"))) & CStr(Format(CTR1, "YYYY")) & "bhav.csv"), _ FileFormat:=xlCSV .Close SAVECHANGES:=False End With End If NEXTDAY: Next CTR1 With ERRBOOK .Close SAVECHANGES:=True End With Exit Sub errhandler: If Err.Number = 1004 Then ROWCOUNT = ROWCOUNT + 1 ERRSHEET.Cells(ROWCOUNT, 1).Value = URL 'MsgBox "CANNOT FIND " & URL End If Resume Next End Sub 'My macro ends above "Tom Ogilvy" wrote: on error goto errhandler errhandler: if err.Number = 1004 then end if Resume Next End Sub -- Regards, Tom Ogilvy "BHARATH RAJAMANI" wrote in message ... Does anyone have code to trap run-time error '1004' ? I'd like to trap '1004' in a macro that reads (case-sensitive) csv filenames from a (derivatives exchange) website. TIA !!! Regards, BR -- Manager, International Private Banking, International Banking Group, ICICI Bank East Wing 8th floor South, ICICI Towers, Bandra Kurla Complex, Mumbai India 400051 |
Can run-time 1004 be trapped ?
You have to turn on error trapping BEFORE the error occurs, not after, i.e. it
should be On Error GoTo errhandler Set BHAVCOPY = Workbooks.Open(URL) On Thu, 16 Sep 2004 05:33:11 -0700, "BHARATH RAJAMANI" wrote: thanks Tom !! Can error codes beyond 1000 be trapped? Do I have to clear the Err. object at start of subroutine? My code is as below, however the 1004 run-time error seems to throw up before I can catch it !! Pls advise. Try this macro from 2 OCT 2003 to 31 OCT 2003 as input parameters. (The markets were closed on 2 Oct 2003, so I want to move on to the next trading day & retrieve the EOD file) 'My macro starts here Option Explicit Sub get_all_bhavcopy_From_nse_website() Dim CTR1 As Date Dim START_DT As Date Dim END_DT As Date Dim URL As String Dim ROWCOUNT As Integer Dim BHAVCOPY As Workbook Dim ERRBOOK As Workbook Dim ERRSHEET As Worksheet Dim FILENAME As String Set ERRBOOK = Workbooks.Open("D:\INVESTMENTS\ERRORS.XLS") Set ERRSHEET = ERRBOOK.Worksheets(1) ERRSHEET.Cells(1, 1).Value = "NO BHAVCOPY FOR THESE DAYS" ROWCOUNT = 2 START_DT = InputBox("START FROM DATE:") END_DT = InputBox("END ON DATE:") For CTR1 = START_DT To END_DT If (Weekday(CTR1) = 2 And Weekday(CTR1) <= 6) Then URL = CStr("http://www.nseindia.com/content/historical/DERIVATIVES/" & CStr(Year(CTR1)) & "/" & UCase(CStr(Format(CTR1, "MMM"))) & "/fo" & CStr(Format(CTR1, "D")) & UCase(CStr(Format(CTR1, "MMM"))) & CStr(Format(CTR1, "YYYY")) & "bhav.csv") Set BHAVCOPY = Workbooks.Open(URL) On Error GoTo errhandler With BHAVCOPY .SaveAs FILENAME:=CStr("D:\INVESTMENTS\CSV\fo" & CStr(Format(CTR1, "D")) & UCase(CStr(Format(CTR1, "MMM"))) & CStr(Format(CTR1, "YYYY")) & "bhav.csv"), _ FileFormat:=xlCSV .Close SAVECHANGES:=False End With End If NEXTDAY: Next CTR1 With ERRBOOK .Close SAVECHANGES:=True End With Exit Sub errhandler: If Err.Number = 1004 Then ROWCOUNT = ROWCOUNT + 1 ERRSHEET.Cells(ROWCOUNT, 1).Value = URL 'MsgBox "CANNOT FIND " & URL End If Resume Next End Sub 'My macro ends above "Tom Ogilvy" wrote: on error goto errhandler errhandler: if err.Number = 1004 then end if Resume Next End Sub -- Regards, Tom Ogilvy "BHARATH RAJAMANI" wrote in message ... Does anyone have code to trap run-time error '1004' ? I'd like to trap '1004' in a macro that reads (case-sensitive) csv filenames from a (derivatives exchange) website. TIA !!! Regards, BR -- Manager, International Private Banking, International Banking Group, ICICI Bank East Wing 8th floor South, ICICI Towers, Bandra Kurla Complex, Mumbai India 400051 |
All times are GMT +1. The time now is 02:23 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com