Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copying Files with VBA (XL97; SR-2)

    Is it possible to utilize wildcards in the FileCopy statement? I keep on getting a file name error. The code that I am using is as follows:

    Sub copyit()
    Dim SourceFile, DestinationFile
    SourceFile = "e:hold*.xls" ' Define source file name.
    DestinationFile = "e:holdjunk*.xls" ' Define target file name.
    FileCopy SourceFile, DestinationFile ' Copy source to target.
    End Sub


    Perhaps someone has another way.

    Thanks,
    John

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

    Re: Copying Files with VBA (XL97; SR-2)

    Have a look at <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=vb&Number=45801&page=&view= &sb=&o=&vc=1>this Thread</A> and see if it helps.

    Andrew C

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying Files with VBA (XL97; SR-2)

    Andrew,

    Thanks for the quick response. I am intrigued with Charlotte's response on the thread you suggested to look at. I'm at a loss of how to properly structure the code. Can you assist?

    Thanks,
    John

  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: Copying Files with VBA (XL97; SR-2)

    John,

    Try the following code <pre>Sub fsoCopyFiles()
    Dim fso As Scripting.FileSystemObject
    Set fso = New Scripting.FileSystemObject
    fso.CopyFile "Cir1*.*", "Cir2"
    End Sub</pre>


    Before running it and whilst in the VB environment, select menu item Tools, References and scroll down the list of available references shown looking out for Microsoft Scripting Runtime and if you find it enable it by clicking on the checkbox. I understand that if you have IE 5 or greater you should have that library available.

    The above code should run if you substitute Cir1 and Cir2 with the actual paths you want to work with. If you do not want to overwrite existing files, you need to add the False argument, and have a error handling routine to deal with such situations.

    Hope that works for you

    Andrew

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Copying Files with VBA (XL97; SR-2)

    If it isn't in the list, you can use the browse button to find the file itself--scrrun.dll--in the System32 folder. But did that work in Excel 97? I know that Access didn't want to play nicely with it.
    Charlotte

  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: Copying Files with VBA (XL97; SR-2)

    Charlotte,

    Good point about browsing for the file, as inclusion in th elist is not automatic.

    It worked fine for me in Excel 97.

    Andrew

Posting Permissions

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