Open, Save & Move - Multiple files

Hello,

The below script will open and save all Excel files in the "startdir", but now I am trying to move all of those modified files to the "enddir". I can not use "vaFileName" to move the files because it contains the whole path and file name. The whole point is to move all the modified files into a new directory (enddir) - I don't want a copy left in the "startdir". Any sugestions??


Option Explicit

Sub FindClientExcelFiles()
Dim FS As Office.FileSearch
Dim vaFileName As Variant
Dim startdir
Dim enddir
Dim Foo As Object
Dim iCount As Long

startdir = "C:\Temp\1"
enddir = "C:\Temp\2"

Set FS = Application.FileSearch

With FS
'Clear old search criteria
.NewSearch

'Directory to search
.LookIn = startdir

'Include sub folders in search
.SearchSubFolders = True

'Look for Excel files
.FileType = msoFileTypeExcelWorkbooks

'Doesn't matter when last modified
.LastModified = msoLastModifiedAnyTime

iCount = .Execute

'List the files in the FoundFiles collection
For Each vaFileName In .FoundFiles
Set Foo = Workbooks.Open(vaFileName)
Foo.Save
Foo.Close
'Foo.MoveFile startdir, enddir

Next vaFileName

End With

End Sub

Why are you opening the files?

Take a look at the Name statement.

It's general syntax is as follows:

Name oldpathname As newpathname

Check Excel VBA help for more information.

I am opening the excel files to initialize the marco for the first time and then saving them. The Name Statement will work perfectly. I just need to get the filename out of "vaFileName" so I can save it to the "newpathname" - see below:

Name vaFileName As enddir

vaFileName = "C:\Temp\1\myfile.xls". I need to save the file "myfile.xls" in the "enddir" which is declared as "C:\Temp\2". How do I get the Name Statement to save each file in the "enddir"???

So you just want the file name?

If you have Excel 2000 there is a InStrRev function that will return the position of a character in a string starting from the end.

So you could search the full path name for "\" and use Mid to get the filename.

Please Login or Register  to view this content.

If that function is not available in your version of Excel then here is a UDF that works like it.

Please Login or Register  to view this content.

Thank you very much Norie!! It works great. All the files are opened, saved, closed, and moved to a different folder. Now I just have to work on the Date format because I want the "enddir" to create a new folder with the current date (MMDDYYYY) when moving the files over. Also - is there a way to override duplicate files in the Name statement (Name vaFileName As enddir & newname) because I just want the files automatically over written if they are duplicates in the enddir.

Thanks again,
Brian




Option Explicit

Sub FindClientExcelFiles()
Dim FS As Office.FileSearch
Dim vaFileName As Variant
Dim startdir
Dim enddir
Dim Foo As Object
Dim iCount As Long
Dim newname As Variant

startdir = "C:\Temp\1"
enddir = "C:\Temp\2\"

Set FS = Application.FileSearch

With FS
'Clear old search criteria
.NewSearch

'Directory to search
.LookIn = startdir

'Include sub folders in search
.SearchSubFolders = True

'Look for Excel files
.FileType = msoFileTypeExcelWorkbooks

'Doesn't matter when last modified
.LastModified = msoLastModifiedAnyTime
iCount = .Execute


'List the files in the FoundFiles collection
For Each vaFileName In .FoundFiles
'MsgBox ("found file " & vaFileName)
Set Foo = Workbooks.Open(vaFileName)
Foo.Save
Foo.Close
newname = Mid(vaFileName, InStrRev(vaFileName, "\") + 1)
Name vaFileName As enddir & newname


Next vaFileName

End With

End Sub