Excel 2003 Macro Doesn't Run in Excel 2010 RRS feed

  • Question

  • I used a macro which worked totally fine under Excel 2003. Now, under Excel 2010 it does not run anymore but displays the following error message:

    "Run-time error '445':
    Object doesn't support this action.

    The function of the macro is to open up all other csv file located in the same folder and copy data out of them into the spreadsheet in which the macro is stored.

    When I click on "Debug" the Visual Basic Editor highlights the row which says: Set FilSrch = Application.FileSearch

    Can you help me with this? I've searched for a solution for a long time but could not find anything which worked.

    The following is the start of the macro code

    Sub OpenCSV()
    Dim i As Integer
    ' change this next line to reflect the actual directory
    Const strDir = "C:\Documents and Settings\shekar\"
    Dim ThisWB As Workbook
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim strWS As String

    Set ThisWB = ActiveWorkbook

    Set fs = Application.FileSearch
    With fs
    .LookIn = strDir
    .Filename = "*.csv"
    If .Execute(SortBy:=msoSortByFileName, _
    SortOrder:=msoSortOrderAscending) > 0 Then
    For i = 1 To .FoundFiles.Count
    Set wb = Workbooks.Open(.FoundFiles(i))
    strWS = wb.Sheets(1).Name
    wb.Sheets(1).UsedRange.Copy (ThisWB.Worksheets(strWS).Range("A1"))
    wb.Close False
    Next i
    MsgBox "There were no files found."
    End If
    End With

    End Sub

    • Edited by adcz250 Wednesday, July 4, 2012 10:17 AM
    Wednesday, July 4, 2012 10:17 AM


  • FileSearch feature is removed from Excel .See your other post where I gave a solution.

    • Proposed as answer by Leo_Gao Friday, July 6, 2012 2:24 AM
    • Marked as answer by Leo_Gao Tuesday, July 10, 2012 5:24 AM
    Wednesday, July 4, 2012 11:08 AM