Run macro <iif</i 001.xls exists
 

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

At the moment, the report macro has the capabiliy to transfer the ro
of data I need from the first 1000 orders, 50001.xls right through t
51000.xls. One single line of macro code can open each order in rea
only, copy the appropriate row, paste it into the report and close th
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 th
macro to ignore the line associated with order spreadsheets not yet o
the system. i.e. the macro lines dealing with 50057.xls through t
51000.xls have ' at the start of them.

I would like to get away from using the ' as it requires me to go i
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.
Okane

--
okane
-----------------------------------------------------------------------
okanem's Profile: http://www.excelforum.com/member.php...nfo&userid=930
View this thread: http://www.excelforum.com/showthread.php?threadid=54668

Run macro <iif</i 001.xls exists
 

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.

Run macro <iif</i 001.xls exists
 

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

Run macro <iif</i 001.xls exists
 

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