Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to move files gets an error if it already exists... | Excel Worksheet Functions | |||
MACRO VBA TO SAVE EVEN IF FILE EXISTS | Excel Programming | |||
Macro help - "Save As" when a file already exists | Excel Programming | |||
Exists? | Excel Programming | |||
within a macro how can I suppress the warning pop "A file named xxxx.xls already exists in this location. Do you want to replace it?" | Excel Programming |