Running a macro but no file to open?

[Carly]

We run a report each month which consists of opening a file for a rep area, manipulating the data, saving, then opening the next rep area and doing the same thing.

The problem we have is that there is not always data for every rep area, but we do not know which ones as they may be different each month.

We run the macro by having one module which runs each of the macros in turn:

i.e
Sub Run

Application.Run "reparea1"
Application.Run "reparea2"

End Sub

How do I write into the macro, if there isn't a file for reparea1 to open, stop the macro and continue with reparea2 module.

Please help

Carly

 

[Richie(UK)]

Hi Carly,

Two possibilities occur to me:

1. Incorporate a "On Error Resume Next" statement. (However, you will then not be aware of any other errors that may arise. Depends what else is in your macro.)

2. Include a call to a subroutine that checks whether the workbook exists. Here is an example taken from Dave Hawley's site:

<pre>Sub DoesWorkBookExist()
'''''''''''''''''''''''''''''''
'Written by http://www.Ozgrid.com

'Test to see if a Workbook exists
''''''''''''''''''''''''''''''''
Dim i As Integer

With Application.FileSearch
.LookIn = "C:MyDocuments"
'* represents wildcard characters
.FileName = "Book*.xls"
If .Execute > 0 Then 'Workbook exists
MsgBox "There is a Workbook."
Else 'There is NOt a Workbook
MsgBox "The Workbook does not exist"
End If
End With
End Sub</pre>

HTH

 

[Carly]

That worked perfectly

Thanks!!!