Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wildcard in Excel for Mac to open ALL files
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wildcard in Excel for Mac to open ALL files
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:Neon:Desktop: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 "Neon520" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wildcard in Excel for Mac to open ALL files
Neon520 wrote...
.... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wildcard in Excel for Mac to open ALL files
Hi Joel,
It's great to hear from you again! The MacId("XLS8") works. And if you notice: Instead of Folder = ":Users:Neon:Desktop: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:Neon:Desktop: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:Neon:Desktop: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 "Joel" wrote: 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:Neon:Desktop: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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy files by name patterns with wildcard | Excel Programming | |||
How to change default Open/Files of Type to "Microsoft Excel Files | Excel Discussion (Misc queries) | |||
how can I specific a folder with wildcard criteria and excel will import all the correct files in that folder? | Excel Discussion (Misc queries) | |||
how can I specific a folder with wildcard criteria and excel will import all the correct files in that folder? | Excel Programming | |||
Using wildcard for checking whether files are open | Excel Programming |