Consulting

Results 1 to 16 of 16

Thread: [MAC] How to process all XLS files in a directory?

  1. #1

    [MAC] How to process all XLS files in a directory?

    Hello,

    I have the following codes to process all XLS files in "/Users/lcw/Desktop/excel", but it does not work for unopened xls files (it seems like the entire Application.FileSearch mechanism does not work at all). Can someone give me a hint how to make it work?

    Thank you!

    Chi-Wai
    _______________________________
    [VBA]
    Sub RunCodeOnAllXLSFiles()
    Dim lCount As Long
    Dim wbResults As Workbook
    Dim wbCodeBook As Workbook

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False

    On Error Resume Next

    Set wbCodeBook = ThisWorkbook

    With Application.FileSearch
    .NewSearch
    'Change path to suit
    .LookIn = "/Users/lcw/Desktop/excel"
    .FileType = msoFileTypeExcelWorkbooks
    .Filename = "*.xls"

    If .Execute > 0 Then
    For lCount = 1 To .FoundFiles.Count
    Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)

    Sheets("US").Select
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp

    wbResults.Close SaveChanges:=True

    Next lCount
    End If
    End With

    On Error GoTo 0
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    End Sub
    [/VBA]

  2. #2
    I am sorry to bring this up again. Can someone point me to a right direction for this problem?

    Any assistance would be greatly appreciated! Thanks!

  3. #3
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by myonmine
    I am sorry to bring this up again. Can someone point me to a right direction for this problem?

    Any assistance would be greatly appreciated! Thanks!
    Can you use FileSystemObject on the Mac (probably not)?

  4. #4
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    Moved to the Mac Users Help Forum
    You should be able to get help here specifically for the Mac
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  5. #5
    VBAX Tutor
    Joined
    Mar 2005
    Posts
    268
    Location
    From the VBA help files in Mac Office:

    ? In Windows, use the FileSearch property to return the FileSearch object for complex file searches. On the Macintosh, use the FileFind property to return the FileFind object for the same purpose.

  6. #6
    I rewrote my macro, but I got "runtime error 445".
    I think FileFind object may not be supported.. It's weird because I am using Excel 2004 version 11.2.5. Can someone tell me the cause of the problem?

    Thank you!

    [VBA]Sub test()
    Dim i As Integer
    Dim wbResults As Workbook

    With Application.FileFind
    .SearchPath = "My Computer:Users:lcwesktop:excel"
    .FileType = msoFileTypeExcelWorkbooks
    .Filename = "*.xls"
    .SearchSubFolders = False
    .Execute

    With .FoundFiles
    Debug.Print "Count = " & .Count

    If .Count > 0 Then
    For i = 1 To .Count
    Set wbResults = Workbooks.Open(Filename:=.Item(i), UpdateLinks:=0)
    Sheets("US").Select
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp
    wbResults.Close SaveChanges:=True
    Next i
    End If
    End With
    End With

    End Sub[/VBA]

  7. #7
    VBAX Tutor
    Joined
    Mar 2005
    Posts
    268
    Location
    If I had to guess, I'd say your problem is in either .FileType or .FileName, probably the latter. I don't think that wildcards are supported. And .FileType may need to be set with MacID() - I've never tried it so I'm not sure.

  8. #8
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    The FileSearch object should work for you. With these changes, do you still error...

    [vba]Sub test2()
    Dim wbResults As Workbook, i As Long, sPath As String, sName As String
    With Application.FileSearch
    .LookIn = "My Computer:Users:lcwesktop:excel"
    .FileType = msoFileTypeExcelWorkbooks
    .Filename = "*.xls"
    .SearchSubFolders = False
    .NewSearch
    .Execute
    Debug.Print "Count = " & .FoundFiles.Count
    If .FoundFiles.Count > 0 Then
    For i = 1 To .FoundFiles.Count
    sPath = .FoundFiles(i)
    sName = Right(sPath, Len(sPath) - InStrRev(sPath, Application.PathSeparator))
    If WbOpen(sName) Then
    Set wbResults = Workbooks(sName)
    Else
    Set wbResults = Workbooks.Open(Filename:=sPath, UpdateLinks:=0)
    End If
    wbResults.Sheets("US").Rows("1:1").Delete
    wbResults.Close SaveChanges:=True
    Next i
    End If
    End With
    End Sub

    Function WbOpen(wbName As String) As Boolean
    On Error Resume Next
    WbOpen = Len(Workbooks(wbName).Name)
    End Function[/vba]

    ??

  9. #9
    VBAX Tutor
    Joined
    Mar 2005
    Posts
    268
    Location
    The FileSearch object does not exist on Mac Office. You are deviously advised to use the FileFind object. Deviously, because according to the link below, MS has disabled FileFind in Office 2004, and it was very buggy in Office X (I myself can't get it to work right in Excel X).

    Read down the thread for some possible workarounds....

    http://www.mcse.ms/archive262-2005-5-1607068.html

  10. #10
    Mac Moderator VBAX Expert shades's Avatar
    Joined
    May 2004
    Location
    Kansas City, USA
    Posts
    638
    Location
    So, either MS should make VBA on the Mac on par with Windows or discontinue VBA?

    Oh, wait, they just announced that they were discontinuing VBA in future releases of Mac Office.

    Software: LibreOffice 3.3 on Mac OS X 10.6.5
    (retired Excel 2003 user, 3.28.2008 )
    Humanware: Older than dirt
    --------------------
    old, slow, and confused
    but at least I'm inconsistent!

    Rich

  11. #11
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Garrrr! The FileSearch method was also discontinued in Access 2007. Amazing how MS can just drop what they want whenever they want. Frustrating..

  12. #12
    VBAX Tutor
    Joined
    Mar 2005
    Posts
    268
    Location
    Quote Originally Posted by shades
    MS should make VBA on the Mac on par with Windows
    Well, according to firefytr, they are..... in a reverse kind of way.

  13. #13
    Mac Moderator VBAX Expert shades's Avatar
    Joined
    May 2004
    Location
    Kansas City, USA
    Posts
    638
    Location
    Zack comes through with the best insight!

    Software: LibreOffice 3.3 on Mac OS X 10.6.5
    (retired Excel 2003 user, 3.28.2008 )
    Humanware: Older than dirt
    --------------------
    old, slow, and confused
    but at least I'm inconsistent!

    Rich

  14. #14
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    LOL! I aim to please.

  15. #15
    Mac Moderator VBAX Expert shades's Avatar
    Joined
    May 2004
    Location
    Kansas City, USA
    Posts
    638
    Location
    BlueCactus, don't know if you have followed the blogs by Scwieb and Rick Schaut (both MacBU developers/managers). But Rick specifically is asking:

    what we really need from users/customers now is information on how they use VBA, and what problems they solve by using VBA.

    Perhaps you can post on his blog your constructive insights and frustrations using AppleScript.

    Rich Schaut blog

    Software: LibreOffice 3.3 on Mac OS X 10.6.5
    (retired Excel 2003 user, 3.28.2008 )
    Humanware: Older than dirt
    --------------------
    old, slow, and confused
    but at least I'm inconsistent!

    Rich

  16. #16
    VBAX Tutor
    Joined
    Mar 2005
    Posts
    268
    Location
    Quote Originally Posted by shades
    BlueCactus, don't know if you have followed the blogs by Scwieb and Rick Schaut (both MacBU developers/managers). But Rick specifically is asking:

    what we really need from users/customers now is information on how they use VBA, and what problems they solve by using VBA.

    Perhaps you can post on his blog your constructive insights and frustrations using AppleScript.

    Rich Schaut blog
    Ahhh... will do. Unfortunately, my initial thoughts - the same damned things that Windows users do - is probably not what they're looking for.

Posting Permissions

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