Solved: FileDialog in Access???

[DarkSprout]

I was just playing with the FileDialog object, But, I couldn't get it to work, All I get is:

! Compile Error
User-defined type not defined

What am I doing wrong & what refs do I need - I'm testing in MSAccess 2003

[orange]

I was just playing with the FileDialog object, But, I couldn't get it to work, All I get is:

! Compile Error
User-defined type not defined

What am I doing wrong & what refs do I need - I'm testing in MSAccess 2003

Not sure what could be wrong. Do you have a reference set to
MS Office Object Library ( I think version 11 or 12 works)?

Does the error tell you which variable/object/type it is complaining about?

[DarkSprout]

Yup! That was the trick
Ref required:= Microsoft Office 11.0 Object Library

Thanks...

[DarkSprout]

I like it so much, I've made a multi purpose version:
[vba]Public Function OpenFileDialog(DisplayText As String, FilterText As String, ParamArray Filter()) As Variant
'// Ref required:= Microsoft Office 11.0 Object Library
'// Returns an array of selected items
'// ToUSe:
'// ItemArray() = OpenFileDialog("Please Select...","Images","*.gif","*.jpg","*.png","*.bmp")
Dim fd As FileDialog
Dim vrtSelectedItem As Variant
Dim arySelectedItems() As String
Dim strFilters As String
Dim i As Integer
Set fd = Application.FileDialog(msoFileDialogFilePicker)

For i = LBound(Filter) To UBound(Filter)
strFilters = strFilters & IIf(i > LBound(Filter), ";", "") & Filter(i)
Next i

i = 0
With fd
.Title = DisplayText
.Filters.Add FilterText, strFilters, 1
.AllowMultiSelect = True
.InitialFileName = CurrentProject.path
'Use the Show method to display the File Picker dialog box and return the user's action.
'The user pressed the button.
If .Show = True Then
ReDim arySelectedItems(.SelectedItems.count)
'Step through each string in the FileDialogSelectedItems collection.
For Each vrtSelectedItem In .SelectedItems
arySelectedItems(i) = vrtSelectedItem
'Debug.Print arySelectedItems(i) ' <-- used for testing
i = i + 1
Next vrtSelectedItem
End If
End With

'Set the object variable to nothing.
Set fd = Nothing
OpenFileDialog = arySelectedItems
End Function[/vba]

[CreganTur]

Question... how would you return the filepath of a single item- I'm using the File Dialog with [VBA]AllowMultiSelect = False[/VBA] because I only want the users to be able to select one item at a time.

[orange]

Question... how would you return the filepath of a single item- I'm using the File Dialog with [vba]AllowMultiSelect = False[/vba] because I only want the users to be able to select one item at a time.

Are you using the same FileDialog as in my example (page 1 of this thread)?

If do, then yes
[vba]AllowMultiSelect = False[/vba]
will limit '.SelectedItems' to just 1 value --> the one you selected.

It brings the whole file ---- filepath and file.

Good luck.

[CreganTur]

Orange,

thanks, but let me rephrase my question- how can I capture the result (filepth) in a variable so that I can use it later?

[orange]

Orange,

thanks, but let me rephrase my question- how can I capture the result (filepth) in a variable so that I can use it later?

Here is code that shows
-creating a variable to hold the returned Filepath
-assigning a value to the variable
-using that variable later.

You could store the value in a temp table or in a file external to Access.

Sub Main()
    Dim mFileInfoForLater As String 'create the variable to hold the value
     '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 aString,
     '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
        .Title = "Select MDB Names"              '<--addition
        .Filters.Add "MDBLog Files", "*.txt"     '<--addition
        .AllowMultiSelect = False
        .InitialFileName = CurrentProject.Path
         'Use the Show method to display the File Picker dialog box and return the user's action.
         'The user pressed the 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 displays the path in a message box.
                
   '  DoCmd.TransferText acImportDelim, "MDBLog_Import Specification", "A2Kmdbs", vrtSelectedItem, True

mFileInfoForLater = vrtSelectedItem '<<<< assigned for use later
     
     MsgBox "Imported Database Names from: " & vrtSelectedItem
            Next vrtSelectedItem
             
        Else
        End If
    End With
    
     'Set the object variable to nothing.
    Set fd = Nothing

     Debug.Print mFileInfoForLater '<<<< used later
End Sub

[DarkSprout]

Or maybe somthing a little simpler...

[VBA]'// Code On Form
Sub MySub()
Dim strHoldPath As String
strHoldPath = OpenFileDialog()

'other code ...


End Sub

'// Place In A Module
Public Function OpenFileDialog() As String
'// Single file return
'// Ref required:= Microsoft Office 11.0 Object Library

Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)

With fd
.Title = "Select Review Planning Excel Sheet"
.Filters.Add "Excel Files", "*.xls", 1
.AllowMultiSelect = False
.InitialFileName = CurrentProject.path
If .Show = True Then
OpenFileDialog = .InitialFileName & ".xls"
End If
End With

Set fd = Nothing
End Function[/VBA]

[orange]

Or maybe somthing a little simpler...

[vba]'// Code On Form
Sub MySub()
Dim strHoldPath As String
strHoldPath = OpenFileDialog()

'other code ...


End Sub

'// Place In A Module
Public Function OpenFileDialog() As String
'// Single file return
'// Ref required:= Microsoft Office 11.0 Object Library

Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)

With fd
.Title = "Select Review Planning Excel Sheet"
.Filters.Add "Excel Files", "*.xls", 1
.AllowMultiSelect = False
.InitialFileName = CurrentProject.path
If .Show = True Then
OpenFileDialog = .InitialFileName & ".xls"
End If
End With

Set fd = Nothing
End Function[/vba]

DarkSprout,

I don't think your sample works. I've tried it and it returns
the correct drive\folder, but it adds on the starting
subfolder and filter extension.

If My currentproject.path is D:\a2K
and I select a file say I:\Book1.xls from the dialog,
the sample returns
I:\A2k.xls ????

As a side issue, do you get the Intellisense assist when using
the filePicker??? I don't and I'm not sure why.

XP Pro SP2 /Access 2003

[CreganTur]

Thanks for the help orange- it's working perfectly now.