Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2004
    Location
    London
    Posts
    64

    Unanswered: Copy & Paste Problem

    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

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    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!
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    Home | Blog

  4. #4
    Join Date
    Sep 2004
    Location
    London
    Posts
    64
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •