Wildcard in Excel for Mac to open ALL files

[Neon520]

Hi Everyone,

Does anyone know what the WILDCARD is to open ALL files in Excel for Mac?

As I get to know this from Joel, one of the great contributors to the forum,
the wildcard for PC Excel is *.xls.
But I don't know what the wildcard is for the Excel for Mac.

I'm trying to open ALL Excel files in a folder to extract some data in them,
but I need to know the wildcard to open all of them.

HELP!!!
Neon520

 

[Joel]

Do a search in VBA help for "DIR Function" and then also look at MACID.

I modified the last code that I posted as follows

1) Added the Colons into the Folder name instead of the slashes I originally
had.
2) Didn't added the : to the end of Folder. Dir() on Mac wants a folder
name not a fileName.
3) On the workbook.Open added the : to the filename which includes the
folder name.
4) Modified the Dir() statement to use MacId("XLS8"). Not sure if XLS8 is
correct since the HELP says it is for Excel 97. Youare probably using Excel
11 and I'm not sure if XLS8 is correct.



Sub Transfer()
'
' Transfer Macro
'
' Keyboard Shortcut: Option+Cmd+x
'

Set NewSht = ThisWorkbook.ActiveSheet

Folder = ":Users:Neonesktop:TEST FOLDER"
FName = Dir(Folder, MacID("XLS8"))

MsgBox ("Found file : " & FName)
NewRowCount = 1
Do While FName <> ""
Set OldBk = Workbooks.Open(Filename:=Folder & ":" & FName)
For Each Sht In OldBk.Sheets
MsgBox ("check Sheet : " & Sht.Name)
With Sht
OldRowCount = 1
Do While .Range("B" & OldRowCount) <> ""
If UCase(.Range("B" & OldRowCount)) = "DECEMBER" Then
.Rows(OldRowCount).Copy _
Destination:=NewSht.Rows(NewRowCount)
NewRowCount = NewRowCount + 1
End If
OldRowCount = OldRowCount + 1
Loop
End With
Next Sht
OldBk.Close savechanges:=False
FName = Dir()
MsgBox ("Found file : " & FName)
Loop

End Sub

 

[Harlan Grove]

Does anyone know what the WILDCARD is to open ALL files in Excel for Mac?

....

Assuming you're running Mac OS X, you either should be able to use
Unix shell file wildcards or there could be no support whatsoever for
wildcards. Try a test: save a blank workbook in a new directory under
the filenames 1, 2 and 345 (that's two 1-character filenames and one 3-
character filename); close these files; then open using * (just the
asterisk) as the filename. If that works, you could use * as the
filename to open all files in a given directory (and maybe all
subdirectories - Unix shell wildcard expansion works that way).

I don't believe Mac OS X (or previous versions) used filename
extensions. They use other means of determining file type (maybe
metadata in directory entries, maybe the magic number in the first few
bytes of every file, something else?). That means there's no
equivalent for *.xls in Windows, which would limit itself to all files
ENDING with .xls, so just Excel .xls files. If you have nothing but
Excel files in the folders from which you'd need to open files, no
problem if * works as the filename. But if you could have many
different file types, you may be stuck having to use a macro to do
this. At that point, you may be better off following up in the Mac-
specific microsoft.public.mac.office.excel newsgroup.

 

[Neon520]

Hi Joel,

It's great to hear from you again!

The MacId("XLS8") works. And if you notice:
Instead of Folder = ":Users:Neonesktop:TEST FOLDER"
and Set OldBk = Workbooks.Open(Filename:=Folder & ":" & FName)

I played around with the code a bit, and here is the setting that works:
Folder = "Users:Neonesktop:TEST FOLDER:"
Set OldBk = Workbooks.Open(Filename:=Folder & FName)

However, there is one problem to this though. Since I put Workbook2.xls
(the one with the code that all the data from other workbooks in the folder,
the code also tries to read Workbook2, which become an Error. Is there a way
to fix this, or it might be better off putting Workbook2 in a subfolder or
somewhere else to avoid this?

Thanks,
Ny



Sub Transfer()
'
' Transfer Macro
'
' Keyboard Shortcut: Option+Cmd+x
'

Set NewSht = ThisWorkbook.ActiveSheet

Folder = "Users:Neonesktop:TEST FOLDER:"
FName = Dir(Folder, MacID("XLS8"))

MsgBox ("Found file:" & FName)
NewRowCount = 1
Do While FName <> ""
Set OldBk = Workbooks.Open(Filename:=Folder & FName)
For Each Sht In OldBk.Sheets
MsgBox ("check Sheet : " & Sht.Name)
With Sht
OldRowCount = 1
Do While .Range("A" & OldRowCount) <> ""
If UCase(.Range("A" & OldRowCount)) = "DECEMBER" Then
..Rows(OldRowCount).Copy _
Destination:=NewSht.Rows(NewRowCount)
NewRowCount = NewRowCount + 1
End If
OldRowCount = OldRowCount + 1
Loop
End With
Next Sht
OldBk.Close savechanges:=False
FName = Dir()
MsgBox ("Found file : " & FName)
Loop

End Sub