Get File Path using Access File Browser feature

[Paulc]

Hi using Access 2003

I would like the user to select a file using the browse folder/file dialogue
box.
When a file is selected, I need the path and file name added to a list box.
The user can repeat the process as required.

I have a form with a button placed on it.

After searching the help files, I copied the following from the access help
page into the forms vba page as the buttons click event.

When i click the button, it invokes a MS VBA alert box stating..

Compile error:
User-defined type not defined.

I have absolutely no idea why.
any ideas appreciated as how how this probmlem can be resolved.

Thanks, Paul.


Sub Main()

'Declare a variable as a FileDialog object.
Dim fd As FileDialog

'Create a FileDialog object as a File Picker dialog box.
Set fd = Application.FileDialog(msoFileDialogFilePicker)

'Declare a variable to contain the path
'of each selected item. Even though the path is a String,
'the variable must be a Variant because For Each...Next
'routines only work with Variants and Objects.
Dim vrtSelectedItem As Variant

'Use a With...End With block to reference the FileDialog object.
With fd

'Allow the selection of multiple files.
.AllowMultiSelect = True

'Use the Show method to display the file picker dialog and return
the user's action.
'If the user presses the action button...
If .Show = -1 Then

'Step through each string in the FileDialogSelectedItems
collection.
For Each vrtSelectedItem In .SelectedItems

'vrtSelectedItem is a String that contains the path of each
selected item.
'You can use any file I/O functions that you want to work
with this path.
'This example simply displays the path in a message box.
MsgBox "Selected item's path: " & vrtSelectedItem

Next
'If the user presses Cancel...
Else
End If
End With

'Set the object variable to Nothing.
Set fd = Nothing

End Sub

 

[Douglas J. Steele]

You're far better off using the API approach demonstrated in
http://www.mvps.org/access/api/api0001.htm at "The Access Web". It's far
less problematic.

As to your specific question, go into Tools | References (from within the VB
Editor) and see whether you've got a reference set to the Microsoft Office
11.0 Object Library.

If you don't, scroll through the list until you find it, select it and back
out of the dialog.

If you do, are any of the selected references marked as MISSING:?

 

[RoyVidar]

Paulc wrote in message

Hi using Access 2003

I would like the user to select a file using the browse folder/file dialogue
box.
When a file is selected, I need the path and file name added to a list box.
The user can repeat the process as required.

I have a form with a button placed on it.

After searching the help files, I copied the following from the access help
page into the forms vba page as the buttons click event.

When i click the button, it invokes a MS VBA alert box stating..

Compile error:
User-defined type not defined.

I have absolutely no idea why.
any ideas appreciated as how how this probmlem can be resolved.

Thanks, Paul.


Sub Main()

'Declare a variable as a FileDialog object.
Dim fd As FileDialog

'Create a FileDialog object as a File Picker dialog box.
Set fd = Application.FileDialog(msoFileDialogFilePicker)

'Declare a variable to contain the path
'of each selected item. Even though the path is a String,
'the variable must be a Variant because For Each...Next
'routines only work with Variants and Objects.
Dim vrtSelectedItem As Variant

'Use a With...End With block to reference the FileDialog object.
With fd

'Allow the selection of multiple files.
.AllowMultiSelect = True

'Use the Show method to display the file picker dialog and return
the user's action.
'If the user presses the action button...
If .Show = -1 Then

'Step through each string in the FileDialogSelectedItems
collection.
For Each vrtSelectedItem In .SelectedItems

'vrtSelectedItem is a String that contains the path of each
selected item.
'You can use any file I/O functions that you want to work
with this path.
'This example simply displays the path in a message box.
MsgBox "Selected item's path: " & vrtSelectedItem

Next
'If the user presses Cancel...
Else
End If
End With

'Set the object variable to Nothing.
Set fd = Nothing

End Sub

Try setting a reference to the Microsoft Office <version> Object
Library

 

[Paulc]

Thank you for your response. It worked perfectly!

I've now have the selected files appearing in the listbox on the form,
however, can you provide any guidance on the following:

1. How do I can make multiple list items save to a table attached (in a one
to many realtionship) to the current record.

2. How do i can enable each item in the listbox as a hyperlink, so that when
a user clicks on a list item in the listbox, the original document is opened?

Thank you.
Paul.

 

[Douglas J. Steele]

Hopefully you're not asking how to store multiple file names in a single
field in a table, because that can't be done.

You need to loop through all of the selected files and insert one row into
the many-side table, using SQL statements.

For the second question, once you have the filenames in the listbox, put
code in its AfterUpdate event to use the FollowHyperlink method. If the
listbox doesn't allow multiselect, it'll be something like:

Private Sub MyListBox_AfterUpdate()

Application.FollowHyperlink Me.MyListBox

End Sub

If you do allow multiselect, and you want each of the selected documents to
open at once, it'll be something like:

Private Sub MyListBox_AfterUpdate()
Dim varItem As Variant

For Each varItem In Me.MyListBox.
Application.FollowHyperlink Me.lstIn.ItemData(varItem)
Next varItem

End Sub

Note that this assumes that the filename is the bound column in the listbox
(replace MyListBox with whatever your listbox is named)

 

[Paulc]

Thank you. I have sorted the Hyperlink.

Can i loop through the list and add the path strings (assigned to the
variable GetFilePath) using...

.AddItem GetFilePath

there is however a twist, the target field 'FileList' is in a different
table called Con_DocPath, assuming this was workable, what would be the
correct sytax to prefix .additem GetFilePath

What would be the syntaxt to prefix the above .additem

Paul.

 

[Douglas J. Steele]

I'm not sure I understand what you're asking. If the path string is in
GetFilePath, what difference does it make what table it's in?

 

[Racquel Robinson]

Hello,

I don't understand how to do this?

"Try setting a reference to the Microsoft Office <version> Object Library"

 

[Douglas J Steele]

That means to go into the VB Editor and select Tools | References from the
menu bar. Scroll through the list of available references until you find the
one for Microsoft Office <version> Object Library (where <version> = 9 for
Access 2000, 10 for Access 2002 and 11 for Access 2003). Select it, and back
out of the dialog.

However, as I said elsewhere in this thread, it's far better to use the API
approach shown in http://www.mvps.org/access/api/api0001.htm The File Dialog
interface available through the Office Object Library has some rather
significant short-comings