Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Can run-time 1004 be trapped ?
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default 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


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 41
Default 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



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 41
Default 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



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default 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





Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 863
Default 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




Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Run time 1004 cluckers Excel Discussion (Misc queries) 5 January 22nd 10 09:17 PM
Run-time error: '1004' Bishop Excel Worksheet Functions 2 May 21st 09 04:28 PM
Error not trapped - please help!!! Bura Tino Excel Programming 8 April 18th 04 09:27 PM
Can error 1004 be trapped???? Richard m Excel Programming 3 November 29th 03 02:55 PM
Run time error 1004 Paul B[_6_] Excel Programming 3 October 26th 03 02:14 PM


All times are GMT +1. The time now is 10:16 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement