For Each .... In Selection --- skip to next if not found

[vbacoder]

Hi everyone,

I am using a statement to step through a list of filenames in a list using the following syntax:

For Each filename In selection

.....

Next

Occasionally, the filenames I add to the selection range do not appear in the source folder. When this happens, the macro throws up an error message and stops. If no match is found, I want it to automatically skip to the next filename in the list. I know there is a way to do this, I just do not know the syntax for achieving this.

This is how I think part of it is done, using the .Find statement:

For Each filename In selection

Workbooks.Find filename: = "...blah blah .."

Next

If no match is found, the statement is False, and I then need to add another statement to tell the code to skip to the next in the list if the filename does not appear anywhere in the selection range/list.

I would be very grateful for any suggestions on this.

Many thanks,

vcoder

 

[Datsmart]

Before your "For Each" statement, put this line.

On Error Resume Next

 

[Richard Schollar]

Hi

You can use :

For Each fname In selection
On Error Resume Next
Workbooks.Open fname
If Err<>0 Then Goto NextFname
On Error Goto 0

NextFname:
   Next

 

[vbacoder]

Hi John and Richard!

Thank you very much for your codes and suggestions. I will try both of these.

Many thanks,

vcoder

 

[vbacoder]

Hi Guys,

I tried the following:

For Each filename In Selection
On Error Resume Next
Workbooks.open_...
If Error <> 0 Then Goto NextFname
On Error Goto Continue 

Continue: Call sub_1
               Call sub_2

NextFname: 
   Next

It skips beautifully when the filename is not found in the folder, but when it is found, the file is opened, and the code doesnot proceed to the next line. Instead, it restarts and goes to the next filename in the list. (perhaps the If Error <> 0 part is too broad, so maybe if I equate to an error code corresponding to 'file not found', this may solve the problem)

If filename is present in the folder, I want to open it and 'continue' with the remaining lines in the code - the call statements to other subs in the module. I've tried many variations of the code above, to try to solve the problem, but to no avail.

When I tried John's code - using

On Error Resume Next

before my original

For Each filename In Selection

line, the code skipped erroneous filenames, but because I had my Call sub statements in the FOR loop, it executed these on old files, when an existing filename was not found. So really, on error I want to skip to the next filename without calling the subs, and on error = 0, I want to continue to the call statements.

I would be very grateful for any advice.

Many thanks,

vcoder

 

[Norie]

I would suggest using Dir rather than hiding the error using On Error Resume Next.

If Dir(fname)="" Then
     Msgbox fname & " not found."
Else
     ' code if file exists
End If

I would also suggest not using Goto, not using Continue and not using Call.


Spaghetti code

 

[vbacoder]

Hi Norie,

Thanks for your suggestions. I would prefer to suppress any error messages during execution as I don't want the macro to stop everytime an error is encountered.

Perhaps this might work:

For Each filename In Selection
	If Dir(fname)="" Then Goto NextFname 
Else
	Call sub_1
	Call sub_2

NextFname: 
   Next

Also, please could you explain why you wouldn't use Goto and Call statements. Do these slow down program execution?

Many thanks,

vcoder

 

[Norie]

vcoder

Did you take look at the link I posted?

 

[vbacoder]

Sorry, didn't click on it before replying. I also edited my post whilst you were replying, to add some code that might work. What do you think of this? I am not too concerned with the inefficiency of the code at this point, as I have other, more significant bottlenecks in the program.

Many thanks for your help.

vcoder

 

[vbacoder]

Well, after some trial and error I implemented the following, which now does what I want it to do:

sub file_ops()
On Error GoTo nextfname:
For Each target In Selection
    id = target.Value
    rownumber = rownumber + 1
        Workbooks.Open Filename:="C:\" & id
        Windows("main_excel.xls").Activate
            Call routine_1(rownumber)
            Call routine_2(rownumber)
nextfname: Resume skipcalls:
skipcalls:
Next
End Sub


Sub routine_1(rownumber)

Range("b" & rownumber) = "success"

End Sub

Sub routine_2(rownumber)

Range("c" & rownumber) = "at last!!!"

End Sub

The section:

nextfname: Resume skipcalls: 
skipcalls: 
Next

looks silly, but it was the only way I could get the procedure to work as I wanted - on error, go to next file in list and don't call routines... on success call routines.