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
Printable View
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
Quote:
Originally Posted by DarkSprout
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?
Yup! That was the trick
Ref required:= Microsoft Office 11.0 Object Library
Thanks...
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]
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)?Quote:
Originally Posted by CreganTur
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.
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 showsQuote:
Originally Posted by CreganTur
-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.
Code: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
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]
Quote:
Originally Posted by DarkSprout
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
Thanks for the help orange- it's working perfectly now.