Filecopy in vba macro

[Julio]

Hello,

I am trying to write a vba macro which saves my whole
bunch of excel files using a wild card, Like "*.xls". Is
FILECOPY in vba accepts wildcard, if not, Any suggestion
or ideas on how to save excel files with a wildcard?

Appreciate your help.

Thanks
Julio

 

[John]

Julio,

My experience is that you cannot save files using a
wildcard.

However, you can read the contents of a directory into an
array. You could then loop through the array and move all
of the files. This assumes that the files are not open.
You would copy the files using Windows API calls.

public sub FileList( FilePath as String )

DIM varFileListArray as Variant Array

* the windows api command Dir I think returns a
* Vector(?) array containing a list of file names
varFileListArray = Dir ( FilePath )

DO WHILE More_Files TRUE
Dim i As Integer
' original file location
OldFilePath = "C:\OldFolder\" + varFileListArray
' new file location
NewFilePath = "C:\NewFolder\" + varFileListArray

' move the file
Name OldFilePath As NewFilePath

NEXT i

Obviously that won't compile, but it should give you the
basics enough to figure out exactly what needs to be done.

If the files are open, you can simply call each workbooks
save or save as function.

John.