on errror resume next

whiteghost

Well-known Member
Joined
Nov 22, 2002
Messages
500
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.
Code:
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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

SIXTH SENSE

Well-known Member
Joined
Oct 29, 2003
Messages
1,883
hi!
how about on error goto label?
this will go to specific line when error occurs!
 

pcc

Well-known Member
Joined
Jan 21, 2003
Messages
1,348
check to see if the file exists first.
something like this (not tested)



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

whiteghost

Well-known Member
Joined
Nov 22, 2002
Messages
500

ADVERTISEMENT

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)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
Hi,

Similar to pcc's suggestion (and also untested :wink: )
Code:
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

Well-known Member
Joined
Nov 22, 2002
Messages
500

ADVERTISEMENT

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

Well-known Member
Joined
Oct 29, 2003
Messages
1,883
hi!
care to post the your code!
from Richie(UK) post, i got a little revision here.
Code:
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

Legend
Joined
Feb 17, 2002
Messages
9,700
Whitey, you're missing equal signs. :p :ROFLMAO:

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. :p
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi WhiteGhost:

I am sorry for butting in -- you are already getting some expert help. In building your filename should you not have ...
Code:
Workbooks.Open FileName:="c:\translog\" & strmth & "1.xls"
instead of ...
Code:
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,885
Messages
5,766,936
Members
425,388
Latest member
Cave_Johnson

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