create a number of copies of excel file 1

[sahmiele]

I have a "template" excel file, and I would like to create a number of copies of this file. The names are loaded into an array. Is there a way I can create the files without opening the "template" file, doing a save as, then repeating? It takes too long to run that way. Thanks in advance!

 

[EBGreen]

I would use the Scripting.FileSystemObject and it's .CopyFile method.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[sahmiele]

Could you give me an example of what the code should look like? I need a little more explanation than that. Thanks.

 

[EBGreen]

just search this forum for FileSystemObject and you should find plenty of examples.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[firefytr]

Check out the "Name" method.

-----------
Regards,
Zack Barresse

 

[mintjulep]

Or (gasp!) a DOS batch file.

 

[PHV]

Why not simply the VBA FileCopy source, destination instruction ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[firefytr]

Both the Name and FileCopy method are probably some of the easiest methods. Creating a FSO can be expensive if you only need it for something like this.

-----------
Regards,
Zack Barresse

 

[sozzer]

if you're not gonna change it much, i'll go with mintjulep's suggestion. If you want to make the xlt an xls at the same time, no probs...

 

[firefytr]

I'd still use native VBA over any other method where possible. You'll save time and resources. And I mispoke, the Name statement is for moving/renaming files, not copying them; PHV had the correct method with FileCopy. Here is an example ...

Sub Create_X_Files()
    Dim arrFiles(), i As Long, strPath As String
    arrFiles = Array("Book10.xls", "Book11.xls", "Book12.xls", "Book13.xls")
    strPath = "C:\Documents and Settings\Phoenix\Desktop\"
    For i = LBound(arrFiles) To UBound(arrFiles)
        FileCopy strPath & "Book1.xlt", strPath & arrFiles(i)
    Next i
End Sub

Obviously change the path(s) and name(s) where desired.

HTH

-----------
Regards,
Zack Barresse