Prompt for a directory name in MAC Excel VBA

[tonedeafdave]

Using Excel VBA on the Mac, I need to prompt the user for a directory
(not a file).

I have tried bits of code from the Excel help files, but they simply
don't work. e.g.

1.
fred = Application.Dialogs(xlDialogFindFile).Show (Method show failed,
error 1004)

2.
Application.FindFile (Method findfile failed, error 1004)

3. (from the help file on "Strategies for Developing Cross-Platform
Solutions")
Dim ffFind As FileFind
Set ffFind = Application.FileFind
With ffFind
.Options = msoOptionsNew
.Name = "New"
.SearchPath = "Macintosh HD:Users:Shared"
.Execute
For i = 0 To .FoundFiles.Count - 1
MsgBox .FoundFiles(i)
Next i
End With
('.name' won't compile - among other problems with this code)

QUESTION 1.

Is this because I am using the student version of Excel for Mac? Is
there more stuff in the professional version? I will get it at once if
there is.

QUESTION 2.

Any ideas on how to approach this problem?

QUESTION 3.

Right now I have fallen back on getopenfilename. This only gets files
of course. I want to look for CSV files. It seems there is a code name
for each file type, because MAC does not support wild cards. Does
anyone know the 4 letter code for CSV files? I thought it was CSVn but
that does not seem to work for any single digit n.

 

[Paul Berkowitz]

Using Excel VBA on the Mac, I need to prompt the user for a directory
(not a file).

I have tried bits of code from the Excel help files, but they simply
don't work. e.g.

1.
fred = Application.Dialogs(xlDialogFindFile).Show (Method show failed,
error 1004)

2.
Application.FindFile (Method findfile failed, error 1004)

3. (from the help file on "Strategies for Developing Cross-Platform
Solutions")
Dim ffFind As FileFind
Set ffFind = Application.FileFind
With ffFind
.Options = msoOptionsNew
.Name = "New"
.SearchPath = "Macintosh HD:Users:Shared"
.Execute
For i = 0 To .FoundFiles.Count - 1
MsgBox .FoundFiles(i)
Next i
End With
('.name' won't compile - among other problems with this code)

QUESTION 1.

Is this because I am using the student version of Excel for Mac? Is
there more stuff in the professional version? I will get it at once if
there is.

No, there's no difference.

QUESTION 2.

Any ideas on how to approach this problem?

FindFile doesn't work in OS X Office versions. Instead make use of the
MacScript function to run AppleScript from VBA:

QUESTION 3.

Right now I have fallen back on getopenfilename. This only gets files
of course. I want to look for CSV files. It seems there is a code name
for each file type, because MAC does not support wild cards. Does
anyone know the 4 letter code for CSV files? I thought it was CSVn but
that does not seem to work for any single digit n.

Dim folderPath As String
Dim filePath As String

folderPath = MacScript("choose folder as string")
filePath = MacScript("choose file as string")

'choose folder' will only let you select a folder (directory), not a file
(they're dimmed), and vice versa for 'choose file'.

The resulting string paths are of course Mac style with colons as
separators, which is how you need to use them in VBA in any case.
AppleScript format for folders (directories) end in a colon - you can remove
it i you don't want it: some VBA commands do not expect the final colon. If
you really just want the folder's _name_, not its full path, that's easy to
parse on either VBA or AppleScript, or you can MacScript the Finder to get
you just the name. But for most things in VBA you'll need the full path.

--
Paul Berkowitz
MVP MacOffice
Entourage FAQ Page: <http://www.entourage.mvps.org/faq/index.html>
AppleScripts for Entourage: <http://macscripter.net/scriptbuilders/>

Please "Reply To Newsgroup" to reply to this message. Emails will be
ignored.

PLEASE always state which version of Microsoft Office you are using -
**2004**, X or 2001. It's often impossible to answer your questions
otherwise.

 

[tonedeafdave]

Thank you for the clear reply and useful information. I was getting
discouraged, after getting comfortable on the PC, to find I was so
helpless on the Mac, so this has revived my spirits. Your reply gave me
leads that will be useful in learning more - I did not know about
macscript at all.

Unfortunately, I have just now had to shelve the project because my 900
lines of VBA routinely crash Excel, sometimes when running, sometimes
when I try to save the workbook afterwards. The resulting saved
workbook then becomes useless, crashing routinely soon after startup.
I can repeat the cycle by rescuing the VBA code and rebuilding the
Workbook, but it hardly seems production ready. The same code runs
smoothly on the PC (there were no incompatibilities since I had taken
out the PC code which did not work on the Mac when I made the original
move from the PC).

My presumption is that there is a memory leak in the VBA
implementation, probably triggered by some unusual thing I am doing. If
I had more time I would try cutting out chunks of code in a sort of
binary search to find out if it was some particular statement that was
involved, but that would be a chancy business at best. My client (who
happens to be my daughter) has decided to break a lifelong rule and buy
a PC to run this thing on, rather than wait for the solution.

Thanks again for your help.