on errror resume next

[whiteghost]

I have a macro which opens a file then calls another macro to copy details from the newly opened file nad paste the in a workbook.It repeats trhis operation with twenty different files.

strmth = inputbox("DO IT")
  on error resume next
   Workbooks.Open Filename:"c:\translog\ " & strmth & "1.xls"
    Call trans1
   Workbooks.Open Filename:"c:\translog\ " & strmth & "2.xls"
    Call trans1
   Workbooks.Open Filename:"c:\translog\ " & strmth & "3.xls"
    Call trans1

The code repeats like that until
Workbooks.Open Filename:"c:\translog\ " & strmth & "20.xls"
Call trans1
end sub

If an error happens ( the common error being one othe files is missing)
how can I get the error handling to resume next +1 line ( in other words resume but not with Call trans1)

thanks

 

[SIXTH SENSE]

hi!
how about on error goto label?
this will go to specific line when error occurs!

 

[pcc]

check to see if the file exists first.
something like this (not tested)

yourfile="c:\translog\ " & strmth & "1.xls" 
myfile = Dir(yourfile)
r = 1
Do While myfile <> ""
r = r + 1
myfile = Dir
Loop
' if r= 1 then the file does not exist
if r<>1 then
Workbooks.Open Filename:=yourfile 
    Call trans1 
end if

 

[SIXTH SENSE]

hi!
I just remember the fileSearch Object.

 

[whiteghost]

how about on error goto label?
this will go to specific line when error occurs

I never know which line will cause the error, so going to a specific line on error would not be a solution thanks anyway

yourfile="c:\translog\ " & strmth & "1.xls"
myfile = Dir(yourfile)
r = 1
Do While myfile <> ""
r = r + 1
myfile = Dir
Loop
' if r= 1 then the file does not exist
if r<>1 then
Workbooks.Open Filename:=yourfile
Call trans1
end if

I'll try this. thanks PCC

 

[Richie(UK)]

Hi,

Similar to pcc's suggestion (and also untested :wink: )

Sub Main()
    Const strStartPath As String = "c:\translog\ "
    Dim strmth As String
    Dim iWbk As Integer
    Dim wbkData As Workbook
    
    strmth = InputBox("DO IT")
    
    For iWbk = 1 To 20
        On Error Resume Next
        Set wbkData = Workbooks.Open(FileName:=strStartPath & strmth & iWbk & ".xls")
        On Error GoTo 0
        If Not wbkData Is Nothing Then Call trans1
        'if the workbook has been opened then call the desired sub
        wbkData.Close
    Next iWbk
    
End Sub

Sub trans1()
    MsgBox "Test"
End Sub

HTH

 

[whiteghost]

the problem with ' on error resume next' is that if the first file has copied a range, then when "on error resume next" calls the same macro and what is on the clipboard gets pasted giving a repeated entry>

e.g.
aaa
bbb
bbb
ccc
ddd
eee


the two rows of "bbb" are the result of on error resume next. The CAll macro 2 pastes what is active on the clipboard. is there a way to 'resume + one line" (missing the next Call macro2)

ican do it by making macro2 copy/paste a blank cell at the end of the routine, but that seems messy

 

[SIXTH SENSE]

hi!
care to post the your code!
from Richie(UK) post, i got a little revision here.

Sub Main() 
    Const strStartPath As String = "c:\translog\ " 
    Dim strmth As String 
    Dim iWbk As Integer 
    Dim wbkData As Workbook 
    
    strmth = InputBox("DO IT") 
    
    For iWbk = 1 To 20 
        On Error Resume Next 
        Set wbkData = Workbooks.Open(FileName:=strStartPath & strmth & iWbk & ".xls") 
        On Error GoTo nextLoop 
        If Not wbkData Is Nothing Then Call trans1 
        'if the workbook has been opened then call the desired sub 
        wbkData.Close 
nextloop:
    Next iWbk 
    
End Sub 

Sub trans1() 
    MsgBox "Test" 
End Sub

 

[NateO]

Whitey, you're missing equal signs.

Another possibility may include:

<font face=Courier New>strmth = InputBox("DO IT")
<SPAN style="color:darkblue">On</SPAN> <SPAN style="color:darkblue">Error</SPAN> <SPAN style="color:darkblue">Resume</SPAN> <SPAN style="color:darkblue">Next</SPAN>
Workbooks.<SPAN style="color:darkblue">Open</SPAN> Filename:="c:\translog\ " & strmth & "1.xls"
<SPAN style="color:darkblue">If</SPAN> <SPAN style="color:darkblue">Not</SPAN> <SPAN style="color:darkblue">CBool</SPAN>(Err.Number) <SPAN style="color:darkblue">Then</SPAN> <SPAN style="color:darkblue">Call</SPAN> trans1 <SPAN style="color:darkblue">Else</SPAN> Err.Clear
Workbooks.<SPAN style="color:darkblue">Open</SPAN> Filename:="c:\translog\ " & strmth & "2.xls"
<SPAN style="color:darkblue">If</SPAN> <SPAN style="color:darkblue">Not</SPAN> <SPAN style="color:darkblue">CBool</SPAN>(Err.Number) <SPAN style="color:darkblue">Then</SPAN> <SPAN style="color:darkblue">Call</SPAN> trans1 <SPAN style="color:darkblue">Else</SPAN> Err.Clear
Workbooks.<SPAN style="color:darkblue">Open</SPAN> Filename:="c:\translog\ " & strmth & "3.xls"
<SPAN style="color:darkblue">If</SPAN> <SPAN style="color:darkblue">Not</SPAN> <SPAN style="color:darkblue">CBool</SPAN>(Err.Number) <SPAN style="color:darkblue">Then</SPAN> <SPAN style="color:darkblue">Call</SPAN> trans1 <SPAN style="color:darkblue">Else</SPAN> Err.Clear</FONT>

I think I like the idea of a filesearch here a little more though.. In any case, note, I did not have the decency to test this.

 

[Yogi Anand]

Hi WhiteGhost:

I am sorry for butting in -- you are already getting some expert help. In building your filename should you not have ...

Workbooks.Open FileName:="c:\translog\" & strmth & "1.xls"

instead of ...

Workbooks.Open FileName:="c:\translog\ " & strmth & "1.xls"

there is a blank character between the folder name and the filename -- why?

Let us first get this sorted out , then we can also look at ... instead of your writing 20 statement s to open the workbooks you could use a loop with a couple of lines of code. The on error resume next in my opinion is working fine.