Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copy a DIR in VBA (EXCEL 97/2000)

    Anyone with some quick code to allow full directories of files to be copied? If I had to do it now, I'd use a filefind-call and walk through all the files copying them individually. I'm wondering if there's a quicker way a-la DOS (e.g. copy cnesub cthersubsub)

    Thanks,

    Erik Jan

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy a DIR in VBA (EXCEL 97/2000)

    Andrew,

    Is this working in Excel 97? I thought that the FSO library only became available for use with Office 2000? I can't test it here, but I don't think this will work in Excel 97.

    I think that with the Copy operations you can use an additional parameter 'Overwrite' to declare whether existing files and folders are to be overwritten. The default setting is TRUE, meaning that existing files or folders of the same name will be overwritten without warning message. If you give this parameter the value FALSE then you will encounter error 58 (file already exists) which you can intercept with On Error.

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Copy a DIR in VBA (EXCEL 97/2000)

    Hans,

    I am pretty sure the the Scripting Runtime (which includes the FSO) is installed with I.E. 5 and up, or the later versions of Windows (98, Me, 2000, NT 4.6). It is provided by SCRRUN.DLL, so if that is installed on any windows system, then the runtime is available. In any event I did run it on excel 97 and it worked fine. I know about the default overwriting behaviour, and maybe should have included an optional parameter to deal with cases where the user might not want to overwrite. But then you do not have a copy of the original directory.

    Andrew

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Copy a DIR in VBA (EXCEL 97/2000)

    <P ID="edit" class=small>Edited by Andrew Cronnolly on 08-Dec-01 12:33.</P>The following function will copy folders if passed a source and destination. If the destination does not exist it is created. Files copied will overwrite existing files of the same name, without prompt. <pre>Function CopyFolder(FromFldr As String, ToFldr As String)
    Dim oFso
    Set oFso = CreateObject("Scripting.FileSystemObject")
    oFso.CopyFolder FromFldr, ToFldr
    Set oFso = Nothing
    End Function</pre>

    To copy folders just use CopyFolder Source, Destination, eg. CopyFolder "C:My Documents", "C:Backup Docs".

    Andrew C Edited to remove unneccesary object assignment

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy a DIR in VBA (EXCEL 97/2000)

    The fact that this isn't working on my computer in my office has nothing to do with Excel 97 but with the fact that the OS is Win 95 and/or has I.E. 4.x? Changing from Win 95 to Win 98 or higher will let me use the FSO?

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Copy a DIR in VBA (EXCEL 97/2000)

    Hans,

    I do not think you need to upgrade your OS, even from Win95 to use the FSO. Once you have the scrrun.dll activeX control available and registered on your system. It is not a component of Office, but part of MS scripting technologies. I think you can distribute the control freely, and to set it up on a suitable system copy scrrun.dll to your Sytem32 sub folder and run regsvr32.exe scrrun.dll to register it. Once that is done it should be possible to set a reference to it in VBA or VB, or else use the late binding CreateObject method. Try it and see (and maybe let us know if it worked).

    Andrew

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy a DIR in VBA (EXCEL 97/2000)

    It works! Great!
    Thanks Andrew. This will significantly decrease the number of conflicts with our IT department!!

  8. #8
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy a DIR in VBA (EXCEL 97/2000)

    Thanks all for solving this little problem. Can anyone clarify the "Overwrite" syntax?
    More in general; it seems that this FSO scripting library potentially opens-up quite some additional power. Is there any documentation and/or are examples available how to use this in EXCEL? (HLP-file, URL's, tools/utils etc.)

    Erik Jan

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Copy a DIR in VBA (EXCEL 97/2000)

    Erik,

    As Hans said the default behaviour is to overwrite, and that is what happens when the 3rd argument (optional) is omitted. In the example above using <pre> oFso.CopyFolder FromFldr, ToFldr, False</pre>

    would result in an error if a file already existed in the destination directory. You would need an Error handling routine to deal with that.

    A good starting place for information on the File System Object is at the <A target="_blank" HREF=http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon98/html/vbconprogramminginfsoobjectmodel.asp>MSDN Library</A>.

    Andrew C

Posting Permissions

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