Results 1 to 3 of 3

With Application.Filesearch error in Excel 2010 VBA

Threaded View

  1. #1
    Registered User
    Join Date
    12-11-2012
    Location
    Hyderabad
    MS-Off Ver
    Excel2007
    Posts
    17

    Post With Application.Filesearch error in Excel 2010 VBA

    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.
    Last edited by JBeaucaire; 02-08-2013 at 03:48 AM. Reason: Added code tags, as per forum rules. Don't forget!

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1