How to display common file dialogs?

[grendel99]

Greetings. I'm using Office X on a G4 desktop with OS version 10.4.3.
I have some VBA macros in PowerPoint which I know to work well on
Windows, and now I'm trying to use them on the Mac as well. VBA in
general appears to work fine, but some of my macros don't.

The problem appears to be with common file dialogs. For example, in a
macro on Windows I can do something like this:

Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogOpen)
fd.Show

....which causes a 'File Open' dialog to appear, and everything's great.
The trouble is, Application.FileDialog doesn't seem to even exist on
the Mac -- so what do I do? I can't believe there aren't standard
dialog types in MacOS, so there has to be some way to access them.

Sorry if this is too elementary -- I've looked in the help file and
other places, all to no avail, I'm afraid. Maybe I'm just not
searching for the right terms. Any help would be enormously
appreciated.

Thanks!
-DH

 

[Steve Rindsberg]

Mac PPT's VBA is still at version 5 (ie, equivalent to Office 97), so a number
of things won't fly. Enums, events ...

I don't recall when .FileDialog was ... erm ... revealed on the Windows side.
It's in PPT97 and PPT2000 but hidden. Doesn't appear in PPTX, hidden or not.

I suspect you'll need to create your own dialog (user form) or learn
Applescript.

Greetings. I'm using Office X on a G4 desktop with OS version 10.4.3.
I have some VBA macros in PowerPoint which I know to work well on
Windows, and now I'm trying to use them on the Mac as well. VBA in
general appears to work fine, but some of my macros don't.

The problem appears to be with common file dialogs. For example, in a
macro on Windows I can do something like this:

Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogOpen)
fd.Show

....which causes a 'File Open' dialog to appear, and everything's great.
The trouble is, Application.FileDialog doesn't seem to even exist on
the Mac -- so what do I do? I can't believe there aren't standard
dialog types in MacOS, so there has to be some way to access them.

Sorry if this is too elementary -- I've looked in the help file and
other places, all to no avail, I'm afraid. Maybe I'm just not
searching for the right terms. Any help would be enormously
appreciated.

Thanks!
-DH

================================================
Steve Rindsberg, PPT MVP
PPT FAQ: www.pptfaq.com
PPTools: www.pptools.com
================================================

 

[Jim Gordon MVP]

Hi,

FileDialog is probably a new command introduced in Visual Basic for
Applications version 6. Office on the Mac supports VBA up to version 5.

However, it is possible to display the File Open dialog box
programmatically in VBA on the Mac.

One way is to use VBA to click the File Open button on the standard
toolbar. Check the Excecute command for command bar buttons in help for
an example.

A better way is to create a temporary toolbar, put the File Open button
on it, then use the execute command to display the File Open dialog box.
This way if someone has customized their standard toolbar it will still
work.

Here's some code I use to create a temporary toolbar called Animate Object:

' Create the temporary toolbar and add two command buttons to it
Set TempBar = CommandBars.Add(Name:="AnimateObject")
TempBar.Visible = True

Application.CommandBars("AnimateObject").Controls.Add
Type:=msoControlButton, Id:= _
2746, Before:=1
Application.CommandBars("AnimateObject").Controls.Add
Type:=msoControlButton, Id:= _
750, Before:=2

'Set TempBarButton =
CommandBars("AnimateObject").Controls.Add(Type:=msoControlButton)
'With TempBarButton
'.FaceId = 2746
'.Visible = True
'End With
Set MyBar = CommandBars("AnimateObject")
With MyBar
.Height = 0
.Width = 50
.RowIndex = 3
.Position = msoBarTop
.Visible = False
End With

Notice that the FaceID number and the msoControlButton ID are the same
number. You can find those numbers by finding and downloading FACEID.XLS
(it's available from several MVP web sites - find it with Google). The
number for File Open dialog box is 23.

To get rid of the temporary command bar:
Application.CommandBars("AnimateObject").Delete

To click the first button on the command bar
Application.CommandBars("AnimateObject").Controls(1).Execute

To click the second it would be
Application.CommandBars("AnimateObject").Controls(2).Execute

Just to make sure everything worked OK I just successfully tested with
this code:

Sub tempbar()
' Create the temporary toolbar and add two command buttons to it
Set test = CommandBars.Add(Name:="test")
test.Visible = True

Application.CommandBars("test").Controls.Add
Type:=msoControlButton, Id:= _
23, Before:=1
End Sub

Sub click()
Application.CommandBars("test").Controls(1).Execute
End Sub

-Jim

 

[grendel99]

Thanks for your reply, Jim, that was very good advice. I've succeeded
in making the File Open dialog box appear using your technique, but now
I need to return the filename(s) of the files chosen in the dialog back
to the calling function. For example, in Windows I could do this:

Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogOpen)
fd.Show
Dim szFile as String
szFile = fd.SelectedItems.Item(1)

....thereby storing in szFile the name of the file selected in the
dialog. What would be the corresponding technique on the Mac? (I'm
fearful you'll tell me there isn't one, since we're opening the dialog
through a command bar.) BTW, this business of Mac's VBA only being
version 5 is bizarre; surely Microsoft has the power to port VB 6...

Many thanks!
- DH

 

[Steve Rindsberg]

Thanks for your reply, Jim, that was very good advice. I've succeeded
in making the File Open dialog box appear using your technique, but now
I need to return the filename(s) of the files chosen in the dialog back
to the calling function. For example, in Windows I could do this:

Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogOpen)
fd.Show
Dim szFile as String
szFile = fd.SelectedItems.Item(1)

....thereby storing in szFile the name of the file selected in the
dialog. What would be the corresponding technique on the Mac? (I'm
fearful you'll tell me there isn't one, since we're opening the dialog
through a command bar.)

Ugly ugly kluge, but once it returns from the file open dialog, a file should
be open. You could get its fullname and close it if you don't actually want it
open. Did I mention ugly? If not, I meant to.

BTW, this business of Mac's VBA only being
version 5 is bizarre; surely Microsoft has the power to port VB 6...

It may be more a question of how much demand there is for it as compared to the
demand for other features. I suspect "other features" wins.

================================================
Steve Rindsberg, PPT MVP
PPT FAQ: www.pptfaq.com
PPTools: www.pptools.com
================================================

 

[Paul Berkowitz]

Or you could simply use the MacScript function:

Dim FilePath As String

FilePath = MacScript("(choose file) as string")


That will open an OS Open dialog, to let you choose the file you want to
open, and will return the result as a Mac colon-delimited string (such as
"Macintosh Hard Disk:Folder 1:Folder 2:Some file.ppt"). You could then
proceed to

FilePath.Open

or whatever is the right command for opening the file.

This method would solve the problem of opening via a Command bar in Jim's
method not returning the file path.

MacScript is a command available to VBA on the Mac, for running AppleScripts
in VBA. AppleScript can access virtually all aspects of the system and
Finder, and most applications (as long as they're AppleScriptable, which
most are).



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

 

[Jim Gordon MVP]

Hi again,

Paul's methed is far more elegant than a pure VBA solution.

Although there's no VB6 support on the Mac, and Microsoft is likely to
drop support for VB altogether for both Mac and Windows some day-
http://news.com.com/Developers+slam+Microsofts+Visual+Basic+plan/2100-1007_3-5615331.html

Essentially VB is a dying thing. MacBU decided to just stick with ver 5.

However, as Paul mentioned, VBA on the Mac has a very important feature
not available for VBA on Windows: the ability to run AppleScripts within
VBA and to exchange information between the two languages.

So in this case where there is a hole in the capabilities of VBA,
AppleScript fills the gap and can live happily with VBA code.

-Jim

 

[Bob Greenblatt]

Thanks for your reply, Jim, that was very good advice. I've succeeded
in making the File Open dialog box appear using your technique, but now
I need to return the filename(s) of the files chosen in the dialog back
to the calling function. For example, in Windows I could do this:

Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogOpen)
fd.Show
Dim szFile as String
szFile = fd.SelectedItems.Item(1)

...thereby storing in szFile the name of the file selected in the

dialog. What would be the corresponding technique on the Mac? (I'm
fearful you'll tell me there isn't one, since we're opening the dialog
through a command bar.) BTW, this business of Mac's VBA only being
version 5 is bizarre; surely Microsoft has the power to port VB 6...

Many thanks!
- DH

If you just want to return the file name for a file open dialog use
application.getopenfilename. This will work on BOTH Mac and windows, and
returns the full path of the file selected by the user (or FALSE if the user
cancelled). Unlike msofiledialogopen, it WILL NOT open the file, but just
pass the file name.

However, there is one ugly bug that has remained unfixed for a long time. If
the user uses the disclosure triangles to drill down folders, the returned
path to the selected file will be incorrect. However, if the user double
clicks folders to open them to drill down to get to the file, the returned
path will be correct.

 

[Steve Rindsberg]

If you just want to return the file name for a file open dialog use
application.getopenfilename. This will work on BOTH Mac and windows, and
returns the full path of the file selected by the user (or FALSE if the user
cancelled). Unlike msofiledialogopen, it WILL NOT open the file, but just
pass the file name.

Hi Bob,

This sounds quite useful. Could you post a working snippet that exercises this?

I can't find anything in the Win PPT or Word object browser that suggests it's
implemented there. Or in the Mac PPT X version, for that matter.



================================================
Steve Rindsberg, PPT MVP
PPT FAQ: www.pptfaq.com
PPTools: www.pptools.com
================================================

 

[grendel99]

Thanks a ton to everyone who provided such useful advice. Paul's
MacScript technique turned out to be the way to go, but Jim's VBA
solution was good too and I've filed it away for the future in case it
comes in handy. My "problem" is 100% fixed now and I've learned a lot
doing it. You guys rock!

A final thought:

Would REALBasic have been better than VB for this task? I don't know
much about REALBasic (or about VB either, for that matter -- I'm
normally a C++ engineer, but had to do some fancy things with
PowerPoint for an upcoming conference and VBA seemed to be the way to
do it), but from what I've read it's a very good language and has all
the things one might need, including MS Office support. As Mac &
Office experts, what's your opinion on this? (I ask because of
compatibility issues such as the one I posted to start with, and
because of Jim's remark that VB is dying -- should I look to REALBasic
in the future?) ...I'm sure this is a big topic that's perhaps best
suited for a new thread, but just a quick take for a newcomer like
myself? Feel free to reply to me via an email if you don't want to
post an off-topic reply to this thread.

Thanks again!
- DH

 

[Bob Greenblatt]

Hi Bob,

This sounds quite useful. Could you post a working snippet that exercises
this?

I can't find anything in the Win PPT or Word object browser that suggests it's
implemented there. Or in the Mac PPT X version, for that matter.



================================================
Steve Rindsberg, PPT MVP
PPT FAQ: www.pptfaq.com
PPTools: www.pptools.com
================================================

OOPS, my foot's in my mouth. I missed the beginning of the thread where this
was for Power Point. Getopenfilename only works with Excel.

 

[Steve Rindsberg]

OOPS, my foot's in my mouth. I missed the beginning of the thread where this
was for Power Point. Getopenfilename only works with Excel.

Well darn. Now it's up to you to persuade Bill to add it to the others.
;-)

O well.

================================================
Steve Rindsberg, PPT MVP
PPT FAQ: www.pptfaq.com
PPTools: www.pptools.com
================================================

 

[Steve Rindsberg]

Would REALBasic have been better than VB for this task? I don't know
much about REALBasic (or about VB either, for that matter -- I'm
normally a C++ engineer, but had to do some fancy things with
PowerPoint for an upcoming conference and VBA seemed to be the way to
do it), but from what I've read it's a very good language and has all
the things one might need, including MS Office support. As Mac &
Office experts, what's your opinion on this? (I ask because of
compatibility issues such as the one I posted to start with, and
because of Jim's remark that VB is dying -- should I look to REALBasic
in the future?)

I'd keep VB and VBA in different parts of your head. <g>

They're very similar in many ways, it's true, and while VB's being (been?)
phased out and support is near its end at MS, reports of VBA's death are
somewhat premature. It'll be supported in the next version of Office on PC and
it appears likely that it'll at least be supported as legacy in the version
after that. That carries us out a good six years before we have to worry about
the odor of the corpse. <g>

As to VB ... if you need to write code for both Mac and PC, it's a non-issue.
It's not supported at all on Mac. That's where RealBasic might be useful ...
dual platform support for one code base.


================================================
Steve Rindsberg, PPT MVP
PPT FAQ: www.pptfaq.com
PPTools: www.pptools.com
================================================

 

[Jim Gordon MVP]

This is the kind of question that I picture being answered with
PowerPoint presentations that have colored boxes representing things
like the CLR, Applications, and how they all talk to each other.

You've got a hardware layer at the bottom. The OS talks to the hardware.
Programming languages talk to the OS and applications.

The way it seems to me in my muddled mind is that in the case of office
applications there is an entry point for languages to talk to the office
applications. Any language that knows the object model and how to talk
to it will do. So it seems on the Windows side theres a common runtime
language that all the other languages know and the CLR talks to office
via the CLR or maybe not. VBA might talk directly. I'm not sure.

On the Mac side you can have VBA and AppleScript talking to office. And
maybe the CLR. Paul B has said at MSDN there's documentation that the
CLR works on the Mac (I looked but didn't find that documentation).

REALBASIC is also supported on Mac and Windows, but I don't know how it
fits into the whole scheme of things. Most likely, it too talks to Offie
through the communication point.

Now I am really going out on a limb here because I don't know any of
these for sure. If I am off about the details I apologize. My point is
that REALBASIC may be a good alternative. Someone with REALBASIC
experience might chime in here and fill in the gaps.

As far as the future of VBA and VB with regard to Office, it's really
hard to say. The Office MVPs made a big fuss over the loss of VBA so
Microsoft agreed to keep backwards compatibility for a while.

One thing is clear: XML will be the new file format. Some people will be
able to use XML immediately to accomplish their goals.

The crystal ball for the future is very cloudy. Will VBA and/or VBA
survive? How long? In what version of Office will it first be supplanted
by whatever takes its place? If VBA goes away will the Mac platform be
the first to say bye-bye or will it be Windows? Or will it never go
away? Only time will tell.

-Jim

 

[Steve Rindsberg]

As far as the future of VBA and VB with regard to Office, it's really
hard to say. The Office MVPs made a big fuss over the loss of VBA so
Microsoft agreed to keep backwards compatibility for a while.

The fuss was mostly on the part of the VB MVPs and other VB developers.
Watching that, the VBA folks started asking whether something similar was in
the cards for Office. The answer was along the lines of "No. Not for this
version. And in all probabability VBA will be supported in the version after
that. Beyond that, it's impossible to say."

One thing is clear: XML will be the new file format. Some people will be
able to use XML immediately to accomplish their goals.

XML + ZIP. Not everything will be expressed in XML (it'd be sort of silly to
convert photo images into XML, for example), but it'll all sit inside a ZIP
file.

While I don't expect we'll be able to do just any old thing with this, it opens
up the ability to manipulate and even create PPT files with any programming
language that can parse XML and read/write ZIP files. That doesn't leave very
many languages out, I figure. ;-)