Open FileDialog, Select File, Record Path (1 Viewer)

[GohDiamond]

I was writing a long, cheeky, post about this and the site logged me off so you'll just have to accept the short version instead.

This is the solution:
1) Include the Microsoft Office Reference Library

a. In the VB Window Go to Tools\References
b. Drill down to find the Microsoft Office X.X Object Library and CHECK it
c. if you don't then FileDialog is not available in the default references​

2. Use Application.FileDialog(msoFileDialogFilePicker) instead of what's in the HELP results
3. Assign the variable sPathAndFilename to capture the Path&Filename
4. Insert the results in your table enabling the .ISHYPERLINK
5. Be sure to define the Display and Address (Displayname#Address) to make your result Path work.

I launched this as a test from a Form button with an Event Procedure 'OnClick' but you can put it on the Hyperlink Field if you want with 'OnDoubleClick' so that you don't keep replacing your results 'OnEnter' or 'OnExit'. I'll leave that up to you, but if you need help let me know.

Private Sub Command0_Click()
'References.AddFromFile "C:\System.Windows.Forms.dll"
'Import System.Windows.Forms

Dim dlgOpen As FileDialog
Dim sPathAndFilename As Variant


Set dlgOpen = Application.FileDialog(msoFileDialogFilePicker)

With dlgOpen
.AllowMultiSelect = True
.Show

For Each sPathAndFilename In .SelectedItems
MsgBox "The path is: " & sPathAndFilename
Hyperlink.IsHyperlink = True
Hyperlink = sPathAndFilename & "#" & sPathAndFilename

Next sPathAndFilename



End With
Me.Dirty = False


End Sub​

You'll have a nice little hyperlink to click on from here forward. I use this to link to external Supporting Documents for the related record.

Best of Luck and
CHEERS!
Goh

PS you ought to pay me for this one if it helps you... or at least THANK me

 

[LanHua]

Hi GOH,
Thanks for the code, it was just what I was looking for. Just two questions about it.
1) I put the Event Procedure in the 'OnDoubleClick' in the event tab but the results disappear when I close the form and open it up again - how do I prevent this?
2) Only one hyperlink can be shown in the text box at one time, is there a way I can get more than one hyperlink to show?
Many thanks for your help.