PDA

View Full Version : [MAC] How to process all XLS files in a directory?



myonmine
08-08-2006, 10:37 AM
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
_______________________________

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

myonmine
08-10-2006, 12:35 PM
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!

Bob Phillips
08-10-2006, 12:40 PM
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)?

lucas
08-10-2006, 01:42 PM
Moved to the Mac Users Help Forum
You should be able to get help here specifically for the Mac

BlueCactus
08-10-2006, 05:18 PM
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.

myonmine
08-14-2006, 10:25 AM
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!

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

BlueCactus
08-14-2006, 05:55 PM
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.

Zack Barresse
08-15-2006, 11:33 AM
The FileSearch object should work for you. With these changes, do you still error...

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

??

BlueCactus
08-15-2006, 09:20 PM
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

shades
08-16-2006, 08:53 AM
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. :(

Zack Barresse
08-16-2006, 09:43 AM
Garrrr! The FileSearch method was also discontinued in Access 2007. Amazing how MS can just drop what they want whenever they want. Frustrating..

BlueCactus
08-16-2006, 09:04 PM
MS should make VBA on the Mac on par with Windows
Well, according to firefytr, they are..... in a reverse kind of way. :rotlaugh:

shades
08-17-2006, 10:14 AM
Zack comes through with the best insight! ;) :D

Zack Barresse
08-17-2006, 01:01 PM
LOL! I aim to please. :beerchug:

shades
08-19-2006, 07:12 AM
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 (http://blogs.msdn.com/rick_schaut/archive/2006/08/09/693499.aspx#comments)

BlueCactus
08-19-2006, 07:48 PM
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 (http://blogs.msdn.com/rick_schaut/archive/2006/08/09/693499.aspx#comments)
Ahhh... will do. Unfortunately, my initial thoughts - the same damned things that Windows users do - is probably not what they're looking for. :devil2: