Hi Guys,
While running below code I am getting "Object doesnot support this action" error is comming in this line "With Application.filesearch". could you please help me for solving this issue.
I am posting my code, can anyone help me.
Private Sub CommandButton1_Click()
Dim dteToday As Date
Dim strFileOutputPath1 As String
Dim wbTemp, wbTemp2 As String
dteToday = Date
'to create directory for today
If Len(Dir("P:\Lgroup\Fundacct\Excel\NAC CS\FWD Hedge %\4681\Backup\" & Format(dteToday, "yyyy"), vbDirectory)) = 0 Then
MkDir ("P:\Lgroup\Fundacct\Excel\NAC CS\FWD Hedge %\4681\Backup\" & Format(dteToday, "yyyy"))
End If
If Len(Dir("P:\Lgroup\Fundacct\Excel\NAC CS\FWD Hedge %\4681\Backup\" & Format(dteToday, "yyyy") & "\" & Format(dteToday, "mmm"), vbDirectory)) = 0 Then
MkDir ("P:\Lgroup\Fundacct\Excel\NAC CS\FWD Hedge %\4681\Backup\" & Format(dteToday, "yyyy") & "\" & Format(dteToday, "mmm"))
End If
If Len(Dir("P:\Lgroup\Fundacct\Excel\NAC CS\FWD Hedge %\4683\Backup\" & Format(dteToday, "yyyy"), vbDirectory)) = 0 Then
MkDir ("P:\Lgroup\Fundacct\Excel\NAC CS\FWD Hedge %\4683\Backup\" & Format(dteToday, "yyyy"))
End If
If Len(Dir("P:\Lgroup\Fundacct\Excel\NAC CS\FWD Hedge %\4683\Backup\" & Format(dteToday, "yyyy") & "\" & Format(dteToday, "mmm"), vbDirectory)) = 0 Then
MkDir ("P:\Lgroup\Fundacct\Excel\NAC CS\FWD Hedge %\4683\Backup\" & Format(dteToday, "yyyy") & "\" & Format(dteToday, "mmm"))
End If
wbTemp = "4681 FWD%.xls"
'if wbTemp doesnt exists, popup box appears, and continues on
With Application.FileSearch
.LookIn = "S:\Lgroup\Fundacct\Excel\NAC CS\FWD Hedge %\4681"
.Filename = wbTemp
If .Execute <= 0 Then 'workbook doesnt exist
MsgBox "4681 Cannot be found. Please ensure it is located in S:\Lgroup\Fundacct\Excel\NAC CS\FWD Hedge %\4681"
On Error Resume Next
End If
End With
wbTemp2 = "4683 FWD%.xls"
With Application.FileSearch
.LookIn = "S:\Lgroup\Fundacct\Excel\NAC CS\FWD Hedge %\4683"
.Filename = wbTemp2
If .Execute <= 0 Then 'workbook doesnt exist
MsgBox "4683 Cannot be found. Please ensure it is located in S:\Lgroup\Fundacct\Excel\NAC CS\FWD Hedge %\4683"
On Error Resume Next
End If
End With
strFileOutputPath1 = "S:\Lgroup\Fundacct\Excel\NAC CS\FWD Hedge %\4681\Backup\" & Format(dteToday, "yyyy") & "\" & Format(dteToday, "mmm") & "\4681 FWD%-"
strFileOutputPath1 = strFileOutputPath1 & Format(dteToday, "mmddyyyy")
Dim strFileOutputPath2 As String
strFileOutputPath2 = "S:\Lgroup\Fundacct\4681\Daily\"
'don't know who changed this macro, error keep poping up cuz the directory doesn't exist,
'i modified this one, don't change macro without letting us know <--wesley
strFileOutputPath2 = strFileOutputPath2 & Format(dteToday, "yyyy") & "\"
If FileExists(strFileOutputPath2) = False Then MkDir (strFileOutputPath2)
strFileOutputPath2 = strFileOutputPath2 & Format(dteToday, "mmm") & "\"
If FileExists(strFileOutputPath2) = False Then MkDir (strFileOutputPath2)
strFileOutputPath2 = strFileOutputPath2 & Format(dteToday, "mmddyyyy")
If FileExists(strFileOutputPath2) = False Then MkDir (strFileOutputPath2)
'''''''''''''''''''''''
If FileExists(strFileOutputPath2) = False Then MkDir (strFileOutputPath2)
strFileOutputPath2 = strFileOutputPath2 & "\4681 FWD%-"
strFileOutputPath2 = strFileOutputPath2 & Format(dteToday, "mmddyyyy")
Dim wbk As Workbook
Set wbk = Workbooks.Open("S:\Lgroup\Fundacct\Excel\NAC CS\FWD Hedge %\4681\4681 FWD%.xls")
wbk.SaveAs strFileOutputPath1
wbk.SaveAs strFileOutputPath2
wbk.Close
Set wbk = Nothing
Dim strFileOutputPath3, strFileOutputPath4 As String
dteToday = Date
strFileOutputPath3 = "S:\Lgroup\Fundacct\Excel\NAC CS\FWD Hedge %\4683\Backup\" & Format(dteToday, "yyyy") & "\" & Format(dteToday, "mmm") & "\4683 FWD%-"
strFileOutputPath3 = strFileOutputPath3 & Format(dteToday, "mmddyyyy")
strFileOutputPath4 = "S:\Lgroup\Fundacct\4683\Daily\"
strFileOutputPath4 = strFileOutputPath4 & Format(dteToday, "yyyy") & "\"
If FileExists(strFileOutputPath4) = False Then MkDir (strFileOutputPath4)
strFileOutputPath4 = strFileOutputPath4 & Format(dteToday, "mmm") & "\"
If FileExists(strFileOutputPath4) = False Then MkDir (strFileOutputPath4)
strFileOutputPath4 = strFileOutputPath4 & Format(dteToday, "mmddyyyy")
If FileExists(strFileOutputPath4) = False Then MkDir (strFileOutputPath4)
'don't know who changed this macro, error keep poping up cuz the directory doesn't exist,
'i modified this one <--wesley, 10/01/10. pls let me know if anybody modify this macro
strFileOutputPath4 = strFileOutputPath4 & "\4683 FWD%-"
strFileOutputPath4 = strFileOutputPath4 & Format(dteToday, "mmddyyyy")
Set wbk = Workbooks.Open("S:\Lgroup\Fundacct\Excel\NAC CS\FWD Hedge %\4683\4683 FWD%.xls")
wbk.SaveAs strFileOutputPath3
wbk.SaveAs strFileOutputPath4
wbk.Close
Set wbk = Nothing
End Sub
Thanks in advance.
Bookmarks