Good afternoon everyone,

I have a little problem that i hope you can help me with.

One of my daily processes uses an Excel runner that produces a number of reports and saves them to a local network drive.

As part of the my process i have to transfer a number of these reports onto a USB drive. I have written a couple of simple macros that do a combination of Save As (straight after the report is saved to the local network drive) and opening and saving the file (for files created earlier in the process).

The problem i have is that this process is taking a long time to run, tying up Excel for long periods of time. I was hoping that there was a solution whereby i could run all of my reports and save them to my local drive then run a separate process that copies them from the network drive and pastes them into the USB drive. To do this manually is currently quicker then the automated process, but is open to human error.

Is this something that someone out there can help me with at all.

Thanks

EddiesVoicebox

Howdy. What code are you using for the Save As? Are you saving that to the local drive or the Network drive? Can you copy directly from your desktop to the USB drive?

Or am I just too old and too slow to comprehend? wouldn't be the first time!

You could create a VBScript file to move files from one place to the next..?

Here's a couple of lovely links I was using earlier today in fact!
http://wsh2.freeweb.hu/ch10b.html
http://www.rgagnon.com/wshdetails/wsh-0006.html

Or in fact, you can write all that in an Excel Macro - but I don't think you can schedule that as a task. Depends on our requirements really.

George / Rich,

Thanks for taking the time to look into this for me guys, its much appreciated.

Rich, i was just saving each report twice (once to the network drive, once to the USB). However I did manage to find a reference to a FileCopy command which i tested and it appears to work as i hoped it would. For one of my process's i have managed to drop the time taken to run and save from 20mins to 4 mins doing it this way.

George, thanks for the links, ill have a look through them as its always good to learn new stuff.


For reference, the actual code i used was:

Dim SourceFile, DestinationFile
SourceFile = "P:\Home_MTD_Report_2007.xls"
DestinationFile = "E:\Home_MTD_Report_2007.xls"
FileCopy SourceFile, DestinationFile

Again thanks for your help this afternoon guys, much appreciated.

EddiesVoicebox