Thread: Run macro <i>if</i> 001.xls exists

I have a report report.xls gathering information from many order spreadsheets 50001.xls 50002.xls etc. The number of order spreadsheets changes over time as orders come in.

At the moment, the report macro has the capabiliy to transfer the row of data I need from the first 1000 orders, 50001.xls right through to 51000.xls. One single line of macro code can open each order in read only, copy the appropriate row, paste it into the report and close the order. The next marco line deals with the next order spreadsheet, pasting the important data into the next row in the report

As only 56 orders have been places I use the ' character to tell the macro to ignore the line associated with order spreadsheets not yet on the system. i.e. the macro lines dealing with 50057.xls through to 51000.xls have ' at the start of them.

I would like to get away from using the ' as it requires me to go in and delete them as new orders go on the system.

Is there an IF statement I can use which would do the following

If 50057.xls exists
then open it, copy the line, paste into reports, close 50057.xls
else move onto the next line in the macro.

Hope you can help.
Okanem

Are all the files in the same directory? If so, why not simply use the
FindFile method to get ALL the existing files and manipulate them as
necessary. Failing that, an ON ERROR GOTO statement would probably
resolve it.

Aidan, apologies for my macro shortcomings.

Although all the order spreadsheets are in the same folder, other files are in there too, including a few spreadsheets.

Can you show me how to incorporate the ON ERROR command into the following abbreviated code that is in my Macro

Workbooks.Open Filename:="\\Server\common\50000.xls" etc
Workbooks.Open Filename:="\\Server\common\50001.xls" etc
Workbooks.Open Filename:="\\Server\common\50002.xls" etc

Looking it up online, there seems to be an ON ERROR GOTO NEXT option which I imagine moves to the next line of code. I have tried incorporating it myself without success.

It's RESUME NEXT but otherwise that is the correct approach:-

On Error Resume Next
Workbooks.Open Filename:="\\Server\common\50000.xls" 'etc
Workbooks.Open Filename:="\\Server\common\50001.xls" 'etc
Workbooks.Open Filename:="\\Server\common\50002.xls" 'etc



okanem wrote:
> Aidan, apologies for my macro shortcomings.
>
> Although all the order spreadsheets are in the same folder, other files
> are in there too, including a few spreadsheets.
>
> Can you show me how to incorporate the ON ERROR command into the
> following abbreviated code that is in my Macro
>
> Workbooks.Open Filename:="\\Server\common\50000.xls" etc
> Workbooks.Open Filename:="\\Server\common\50001.xls" etc
> Workbooks.Open Filename:="\\Server\common\50002.xls" etc
>
> Looking it up online, there seems to be an ON ERROR GOTO NEXT option
> which I imagine moves to the next line of code. I have tried
> incorporating it myself without success.
>
>
> --
> okanem
> ------------------------------------------------------------------------
> okanem's Profile: http://www.excelforum.com/member.php...fo&userid=9301
> View this thread: http://www.excelforum.com/showthread...hreadid=546681