RunSavedImportExport on excel worksheet that might or might not exist.

[thestappa]

I have macro that steps through a bunch of saved ImportsExports. My problem is that sometimes the spreadsheets will have more than one worksheet. I added a new line to my macro to begin importing the second worksheet, however, it doesn't always exist. When the second sheet doesn't exist I get an error stating that "WORKSHEET_2$" is not a valid name.

Is there anyway I can just skip that? My only option is to "Stop All Macros".

I have SetWarnings = NO already but that isn't helping.



I'm hoping I don't have to redo my macro all in VBA.

But if I do have to go vba, is there an easy way to check if a worksheet exists?

[JoeM]

I'm hoping I don't have to redo my macro all in VBA.

But if I do have to go vba, is there an easy way to check if a worksheet exists?

Well, I think it is going to require VBA, but the good news is that it really isn't as hard as you think.

Access has a great feature that converts your macros to VBA code for you. So this gives you a great start. Just highlight your Macro and select the "Convert Macros to Visual Basic" in the Visual Basic menu/ribbon. Then view your code.

I think you may be able to bypass those errors with some error handling/trapping. Just before the step that checks for sheet 2, try adding the line:

On Error Resume Next

and then after the part of the code that tries to import sheet 2, add this line to reset it:

On Error GoTo 0

and see if that works.

Trying to determine the number of sheets in each Excel file first is probably a "cleaner" option, but I think it will be a lot more complex, and probably mean having to use some Excel objects in your code. If my suggestion works, it will probably be easier for your to maintain/understand, expecially if you are already leery of VBA code.