Copy & Paste Problem Excel

[eddiesvoicebox]

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

 

[Bong]

If the process is really sufficiently quick writing to your local drive, I would use something other than VBA to copy from c: to the USB drive. Either a .bat file, or (my favorite) Tcl.

_________________
Bob Rashkin

 

[eddiesvoicebox]

Hi Bob,

Thanks for the quick response. Apologies if this is a really simple question but how would i go about this ?

I am afraid i wouldnt have the first clue where to start. I generally make data look pretty and automate manual process's (in Excel/Access).

Is this type of thing something that could be explained fairly easily ?

Again thanks for your response.

EddiesVoicebox

 

[xlbo]

in VBA, you can use the VBScript command "FileCopy"

it is executed like:

FileCopy CopyFrom CopyTo

where CopyFrom is the full path and file name to copy and CopyTo is the path and filename you want to copy to

It is generally very quick

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question