Find File, Save As and Close

[s0m1ght1]

Hi All
I have a workbook with some code which searches the C: for certain files and opens them. I am then trying to save them to another directory and close them. The problem with the code below is it saves the file that is used to search for the files, not the ones I have searched for and opened. Can anyone please help me with this as it is driving me insane. I have tried to find a way to make the files searched for Active but had no luck. See Code Below. Cheers!!!


Sub macro1()
Dim objExcel As Excel.Application
Dim SourceFileName As String

With Application.FileSearch
.NewSearch
.LookIn = "C:\Documents and Settings\"
.SearchSubFolders = True
.Filename = "File A.xls"
.MatchTextExactly = True
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
For j = 1 To .FoundFiles.Count
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open Filename:=.FoundFiles(j)
objExcel.Visible = True
Application.DisplayAlerts = False
SourceFileName = ActiveWorkbook.Name
ActiveWorkbook.SaveAs Filename:="V:\File .xls"
Application.DisplayAlerts = True
Next j
Else
MsgBox "file Not found, please check number and try again"
End If
End With

With Application.FileSearch
.NewSearch
.LookIn = "C:\Documents and Settings\"
.SearchSubFolders = True
.Filename = "File B.xls"
.MatchTextExactly = True
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
For j = 1 To .FoundFiles.Count
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open Filename:=.FoundFiles(j)
objExcel.Visible = True
Application.DisplayAlerts = False
SourceFileName = ActiveWorkbook.Name
ActiveWorkbook.SaveAs Filename:="V:\File B.xls"
Application.DisplayAlerts = True
Next j
Else
MsgBox "file Not found, please check number and try again"
End If
End With
End Sub

 

[phxsportz]

Not quite sure but perhaps this method might work for you...

Untested ...

Sub macro1()
    Dim objExcel As Excel.Application
    Dim SourceFileName As String
    Dim wb As Workbook
    With Application.FileSearch
        .NewSearch
        .LookIn = "C:\Documents and Settings\"
        .SearchSubFolders = True
        .Filename = "File A.xls"
        .MatchTextExactly = True
        .FileType = msoFileTypeAllFiles
        If .Execute() > 0 Then
            For j = 1 To .FoundFiles.Count
                Set objExcel = CreateObject("Excel.Application")
                wb = .FoundFiles(j)
                objExcel.Workbooks.Open Filename:=.FoundFiles(j)
                objExcel.Visible = True
                Application.DisplayAlerts = False
                SourceFileName = ActiveWorkbook.Name
                With wb
                    .SaveCopyAs ("V:\File .xls")
                End With

                'ActiveWorkbook.SaveAs Filename:="V:\File .xls"
                Application.DisplayAlerts = True
            Next j
        Else
            MsgBox "file Not found, please check number and try again"
        End If
    End With
    With Application.FileSearch
        .NewSearch
        .LookIn = "C:\Documents and Settings\"
        .SearchSubFolders = True
        .Filename = "File B.xls"
        .MatchTextExactly = True
        .FileType = msoFileTypeAllFiles
        If .Execute() > 0 Then
            For j = 1 To .FoundFiles.Count
                Set objExcel = CreateObject("Excel.Application")
                wb = .FoundFiles(j)
                objExcel.Workbooks.Open Filename:=.FoundFiles(j)
                objExcel.Visible = True
                Application.DisplayAlerts = False
                SourceFileName = ActiveWorkbook.Name
                With wb
                    .SaveCopyAs ("V:\File .xls")
                End With
                'ActiveWorkbook.SaveAs Filename:="V:\File B.xls"
                Application.DisplayAlerts = True
            Next j
        Else
            MsgBox "file Not found, please check number and try again"
        End If
    End With
End Sub

 

[s0m1ght1]

Thanks for your response mate, but it comes up with the following error

Run Time Error '91':
Object variable or With block variable not set