Getting a Path from File Open Dialog

[The Dow]

I'm trying to use Word's File Open Dialog to have the user select an Excel
file (for linking purposes). I can return the Excel file's NAME, but I can't
seem to return the PATH.

My VBA code is:
With Dialogs(wdDialogFileOpen)
.Display
xlOpen = .Name
End With

What should I use to get the file's path? Also, is there an easy way to
have the File Open Dialog use "all files" as the default for the file type?
Thanks!

 

[Dave Lett]

Hi,

I got this from the article "How to get the full path from the SaveAs
dialog" at http://word.mvps.org/faqs/macrosvba/GetSaveAsPath.htm

With Dialogs(wdDialogFileOpen)
.Display
MsgBox WordBasic.FileNameInfo(.Name, 5)
End With


HTH,
Dave

 

[OughtFour]

"The Dow" asked,...
Dave Lett replied:

I got this from the article "How to get the full path from the SaveAs
dialog" at http://word.mvps.org/faqs/macrosvba/GetSaveAsPath.htm

With Dialogs(wdDialogFileOpen)
.Display
MsgBox WordBasic.FileNameInfo(.Name, 5)
End With

I am in the middle of the same problem as Dow, and found the same solution
as Dave Lett (from the same source, no less).

I am wondering if CurDir() does not provide the same result without dipping
back into the WordBasic epoch.

Does anyone have any experience with that? Is FileNameInfo better?

Thanks!

 

[Jay Freedman]

Dave Lett replied:

I am in the middle of the same problem as Dow, and found the same solution
as Dave Lett (from the same source, no less).

I am wondering if CurDir() does not provide the same result without dipping
back into the WordBasic epoch.

Does anyone have any experience with that? Is FileNameInfo better?

Thanks!

You don't want the Open dialog to actually open the chosen document
(particularly since in Dow's case it isn't a Word document). The
..Display method shows the dialog without executing its "open"
function, exactly what's wanted here -- but it doesn't change the
current directory if the user navigated to a different folder. That
means using CurDir() could return the wrong result.

The FileNameInfo function isn't some kind of evil throwback to the
"WordBasic epoch". The WordBasic object is a legitimate VBA object
that happens to implement almost everything in WordBasic, including
several very useful functions that don't exist in "native" VBA. See
http://www.word.mvps.org/FAQs/MacrosVBA/WordBasicCommands.htm for
documentation.

 

[Herbert]

Hi!

Alternatively you could try something like this ...

Dim fd As FileDialog, sPathAndFilename As String
Dim sFilename As String, sPath As String

Set fd = Application.FileDialog(msoFileDialogFilePicker)
fd.Filters.Add "Excel workbook", "*.xls"
fd.AllowMultiSelect = False
fd.InitialFileName = "C:\tmp"
If fd.Show = -1 Then
' a file has been chosen
sPathAndFilename = fd.SelectedItems(1)
End If

Regards,
Herbert

 

[OughtFour]

The FileNameInfo function isn't some kind of evil throwback to the
"WordBasic epoch". The WordBasic object is a legitimate VBA object
that happens to implement almost everything in WordBasic, including
several very useful functions that don't exist in "native" VBA. See
http://www.word.mvps.org/FAQs/MacrosVBA/WordBasicCommands.htm for
documentation.

No disrespect to these tools intended! I use them as needed. SortArray is
pretty useful.

But I am trying to understand what "FilenameInfo" really does. As far as I
can tell, the following code

MsgBox WordBasic.FileNameInfo$("foo.doc", 5)

displays the same thing as

MsgBox CurDir()

That is, at least with the 5 parameter, the "foo.doc" parameter is
meaningless, and it doesn't matter if "foo.doc" is in the current directory
or for that matter exists at all.

Is that your experience too? Or, is there a useful difference?

 

[Jay Freedman]

No disrespect to these tools intended! I use them as needed.
SortArray is pretty useful.

But I am trying to understand what "FilenameInfo" really does. As far
as I can tell, the following code

MsgBox WordBasic.FileNameInfo$("foo.doc", 5)

displays the same thing as

MsgBox CurDir()

That is, at least with the 5 parameter, the "foo.doc" parameter is
meaningless, and it doesn't matter if "foo.doc" is in the current
directory or for that matter exists at all.

Is that your experience too? Or, is there a useful difference?

To quote Ed McMahon, "You are correct, sir!" I do get the same result in
Word 2003. I suspect this behavior has changed somewhere between Word 97 and
the present, and I hadn't gone back to check it. The comment in the sample
code at http://word.mvps.org/faqs/macrosvba/GetSaveAsPath.htm says:

"If the user typed the path and filename instead of browsing to it, the VBA
function CurDir() won't reflect the path; hence the need to use the
Wordbasic function FileNameInfo to get the path"

But even in that case CurDir() seems to work correctly now.

 

[John]

I couldn't get this to compile. It objected to FileDialog.

Is it possible to have a browser window with a 'Default' button, so
that if you press this after browsing to a folder, after closing then
re-opening the browse window, it start from this default folder?

 

[Herbert]

You have to add "Microsoft Office 10.0 Object Library" to the list of
references.

As for the default folder:
You can use the property "InitialFileName" to set an initial folder for the
user.