Windows 7 - Open a Folder Using VBA

Asked By Stev on 27-Jan-09 04:48 PM
I’m trying to write a macro that will do the same thing as File/Open but it
will open the “file listing dialog box” to a particular folder (e.g.,
z:\word) and then allow me to choose which file to open in that
folder/directory.

When I record the macro, I get to the open dialog box for the desired
folder, but I’m unable to turnoff the recorder at the desired folder.  I can
either cancel the recording or continue until I select a document.  I would
like the macro to stop at the desired folder and allow the use to select the
document to open.

In doing some research, it seems that I need to write a VBA code to
accomplish the task.  I have a very limited knowledge base when it comes to
writing code.

I did find the following code:

Sub OpenFolder()
Application.Dialogs(wdDialogFileOpen).Show
End Sub

It does works; but it opens the last directory/folder that used in Word.

What additional code/lines do I need to include so it opens to the
directory/folder z:\word?

At this point, I’m not even sure if I’m asking the question correctly.  Any
help or suggestions are appreciated.




gordon(dot)bentleymix(at)gmail(dot)com replied on 27-Jan-09 05:23 PM
Steve,

There are probably a multitude of ways to accomplish what you want, but one
of the easiest is something like this:

Sub OpenFolder()
ChangeFileOpenDirectory "z:\word\"
Dialogs(wdDialogFileOpen).Show
End Sub

It's a bit sloppy in that it changes the "file open directory" without
warning the user, and of course, this change will stick until the next time
Word is restarted. However, this isn't much different from what Word does
natively - as you discovered when you said "it opens the last
directory/folder that used in Word".

Other methods are available, and there are ways to work around the
limitation of the above; e.g. recording the current "file open directory" in
a variable and resetting it afterwards. However, these methods might be a bit
more complicated and probably not worth the effort for a limited application.
--
Cheers!

Gordon Bentley-Mix
Word MVP

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.
Larry replied on 28-Jan-09 05:44 AM
You are on the right track, Steve:

''''''''''''''''''''''''''''
Sub OpenFolder()
With Application.Dialogs(wdDialogFileOpen)
.Name = "z:\word"
.Show
End With
End Sub
'''''''''''''''''''''''''''''

Strangely, .Name doesn't show up as a property in the VBA editor, so
if you were counting on the editor to help you guess at these values,
you wouldn't have found this one.
Graham Mayor replied on 28-Jan-09 02:41 AM
You can get the built-in dialog box argument list at
http://msdn.microsoft.com/en-us/library/aa211930(office.11).aspx

--
Graham Mayor -  Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org
Stev replied on 28-Jan-09 10:00 AM
Thanks to all for your help.  This works perfect!