[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]

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!

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)?

Moved to the Mac Users Help Forum
You should be able to get help here specifically for the Mac

From the VBA help files in Mac Office:

Quote:

---

? 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.

---

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:lcw:Desktop: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]

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.

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:lcw:Desktop: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]

??

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

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. :(

Garrrr! The FileSearch method was also discontinued in Access 2007. Amazing how MS can just drop what they want whenever they want. Frustrating..

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. :rotlaugh:

Zack comes through with the best insight! ;) :D

LOL! I aim to please. :beerchug:

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

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. :devil2: