Trapping a file name from userform

[Roderick O'Regan]

I have a userform with a command button. When this button is selected
it first looks for a drive and folder in an INI file.

I use the System.privateprofilestring method for this. All OK so far.

I then want the File Open dialog box to appear and look at the avove
location for users to select a file.

When the user has made the selection and selected the Open command
button I want to trap this filename ("ThisFile", below) and then use
it as follows:
With ActiveDocument.Shapes.AddPicture(Anchor:=Selection.Range,
Filename:= _
ThisFile, LinkToFile:=False, _
SaveWithDocument:=True)
....other properties
End With

I've been using the following command, plus a few others, to
complement it in a module where they can be declared:
Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long

However, it doesn't seem to work in that fashion when written into a
procedure within a userform.

It's the GetOpenFilename that I want to use which is within the
comdlg32.dll library.

How does one do this when a command button is selected wthin a
userform, please?

Or, on the other hand, am I using a sledgehammer to crack a nut?

Regards

Roderick

 

[Jay Freedman]

Hi Roderick,

"...using a sledgehammer to crack a nut" -- yeah, that's about right. You
don't need API calls, as what you need is all built into Word and VBA.

When you call the Open dialog, the dialog's .Name property returns the
selected path/filename (assuming the user didn't cancel the dialog).
Actually, I'd recommend using the InsertPicture dialog instead of the
FileOpen one, as it defaults to the configured Pictures folder and thumbnail
view. Try it this way:

Private Sub cmdOK_Click()
Dim dlg As Dialog
Dim strPicFile As String

Set dlg = Dialogs(wdDialogInsertPicture)
With dlg
If .Display = -1 Then ' not canceled
strPicFile = .Name ' get user's choice
End If
End With
Set dlg = Nothing

If strPicFile <> "" Then
ActiveDocument.Shapes.AddPicture _
FileName:=strPicFile, _
LinkToFile:=False, _
SaveWithDocument:=True, _
Anchor:=Selection.Range
' ... more properties
End If

Unload Me
End Sub

 

[Roderick O'Regan]

Thanks Jay.

After that little lesson I've put the sledgehammer away. Buried it!

I've noticed you used the .Name property for the dialog. Where is that
property listed? And what other properties are available in the Open
dialog?

If one types in the VBE Dialogs(wdDialogFileOpen) followed by the dot
then a list of the available methods appears. The .Name one is not in
the list. In my cynical mind (if I'm correct, that is) if .Name has
been left off what else has?

How, for instance can one show a thumbnails view of the File Open
dialog box using VBA?

Regards

Roderick

 

[Jay Freedman]

Hi Roderick,

It's a secret! ;-)

Well, almost... In the VBA editor, ask the help system for the topic
"Built-in Dialog Box Argument Lists". That shows the available properties
for each dialog. The "IntelliSense" popup in the VBE shows only the
properties and methods of the generic Dialog object, not of the individual
dialogs.

There's an additional problem in that not all of the listed arguments
actually work, and there are a few arguments that aren't listed (I don't
remember which ones offhand). And if you can't guess from the names which
arguments correspond to which widgets in the dialog, there's no official
documentation for that. Google may help with that.

Finally, there simply aren't any properties or methods for a lot of the
things you'd like to do in dialogs, and the view in the Open dialog is one
of them. Sometimes you can work out the sequence of keystrokes to do what
you want, and put that into a SendKeys command, but I couldn't even get that
to work for the view -- I can select the view icon, but the list doesn't
drop down.

 

[Jay Freedman]

By the way, more info is available here:
http://www.word.mvps.org/FAQs/MacrosVBA/WordDlgHelp.htm

 

[Roderick O'Regan]

Thanks for all your help and advice Jay.

Isn't VBA fun?!

By the way, your article below was very helpful. I downloaded the
WordBasic Help file mentioned in it and poked around inside it for
some additional and useful information.

I noticed your comment regarding SendKeys...I found in the MS
Knowledgebase coding for the FileOpen procedure where it was used in a
similar way. Copied it to a routine in my Normal Template VBE, tried
it and SendKeys didn't work either. Oh, well...

Regards

Roderick