Printable View
Hi,
In Excel I use the FileDialog to allow the user to browse to a file which then imports into Excel.
Is there a similar method in Access as the data sources are getting quite large and I want to migrate the process onto Access but still allow the user to browse for the file which will then be copied into an Access table
Thanks
Not that I am aware of and I have no idea why. I think I have seen some code somewhere that can do this, but I can't remember where.
Starting with Access 2002 there is a filedialog object. 97 and 2000 I believe you have to use the API. Here is an example.
http://www.mvps.org/access/api/api0001.htm
I have 2003 and don't have that functionality. Well, at least it doesn't work the way I am used to it working in Excel.
How does this work in Access 2002+ ?
This is from Access 2007 Help File. Of course set a reference to your version of the Office Object Library
[vba]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 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
'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.
MsgBox "The path is: " & vrtSelectedItem
Next vrtSelectedItem
'The user pressed Cancel.
Else
End If
End With
'Set the object variable to nothing.
Set fd = Nothing
End Sub
[/vba]
If you want this for importing files Why don't you use Get External Data on the File Menu?
You have access towhen you set a reference toCode:Application.FileDialog(msoFileDialogFilePicker)
Microsoft Office 12.0 Object Library
This is available in my setup which is Office 2003.
I do not believe it is part of Access itself.
Gotcha. I didn't have any reference to MSO 12.0 OL.Quote:
Originally Posted by orange
Thanks!
Roger
So I only have MSO 11.0 but was able to type in the code: [vba]Application.FileDialog(msoFileDialogFilePicker)[/vba]
But what the heck do I do now? I am trying to load a text file, as new data in a table that is then run against exception queries to create a final table for review but I don't have a clue what I need to do next. Does anyone have an example of this type of funciton being used? Anthing please help.
-Rich
What is it that you are trying to solve usingQuote:
Originally Posted by FrymanTCU
code: [vba]Application.FileDialog(msoFileDialogFilePicker)[/vba] Perhaps there are other ways to achieve it.
InputBox for example.
Well I'm trying to insert a text file into an existing table, deleting the old data. So far I have the following code which opens the Dialog box but I don't know what to do once the file is selected.
[vba]Dim dlgOpen As FileDialog
Set dlgOpen = Application.FileDialog(msoFileDialogFilePicker)
With dlgOpen
.AllowMultiSelect = False
.Show
End With[/vba]
Slightly off-topic...
If you use such a file dialog control to upload a file into an Access binary field, will it insert just the binary contents (read: BLOB) or also add the "OLE Header" or "OLE Object" information with it?
I found this script on another forum, how would I call my file here? I am missing something, I just don't know what to do after the file is selected from the Dialog box....
[vba]Dim dlgOpen As FileDialog
Dim NewUploadFile As String
Set dlgOpen = Application.FileDialog(msoFileDialogOpen)
With dlgOpen
.AllowMultiSelect = False
.Show
End With
Set NewUploadFile = dlgOpen.Execute
DoCmd.RunSQL "Delete * from [CurrentFile]"
'''You should replace the filename with full path information
'''ie c:\data\testaccess.csv, or if a network path \\server\x\data\testaccess.csv
DoCmd.TransferText acImportDelim, "|", [CurrentFile], "'& NewUploadFile'", True
[/vba]
I found this M$ knowledge base article that may help.Quote:
Originally Posted by FrymanTCU
http://support.microsoft.com/kb/279508
Orange, thanks that article was helpful my dialog box works but the text is still not importing properly.
[vba]For Each varFile In .SelectedItems
Me.FileList.AddItem varFile
Next
Else
[/vba]
How would I modify this to only import one file?
[vba]For varFile In .SelectedItems
DoCmd.TransferText acImportDelim, "|", [CurrentFile], & varFile , True
Else
[/vba]
What is happening exactly?Quote:
Originally Posted by FrymanTCU
What error are you getting?
What is the purpose of the "&" in fromnt of varFile in the DoCmd line?
Do you trap errors in the routine?
Can you import the file manually as a test?
I am new to programing and trying to piece together all the stuff I am learning on the web. I a not sure what you mean by 'trap errors'? I am going to change the code to import an Excel file instead, but I am still having a reference error when I try to reference the file selected in the dialog box, I have MSO 11.0 selected, is there something else I need set NewUploadFile = to the file selected?
[vba]DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"Current", NewUploadFile , True[/vba]
Take a look here for error trapping:Quote:
Originally Posted by FrymanTCU
http://www.fmsinc.com/tpapers/vbacode/Debug.asp
Also, no need to move to an excel file, if you have a csv or other text file.
I was concerned with the format of your statement. It looked like you had a variable name with and & in the DOCmd line.
Great link, Thanks again. I was able to trap the following error: function call on left-hand side of assignment must return variant or object. So I am still stuck at getting the file path from the dialog box to the DoCmd procedure.Quote:
Originally Posted by orange
[VBA]
Dim dlgOpen As FileDialog
Dim NewUploadFile As Variant
Set dlgOpen = Application.FileDialog(msoFileDialogFilePicker)
With dlgOpen
.AllowMultiSelect = False
.Title = "Please select the New TSO Download File"
.Filters.Clear
.Filters.Add "Excel Files", "*.XLS"
.Show
If .Show = True Then
.SelectedItems = "NewUploadFile"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel4, [Current], NewUploadFile, True
Else
MsgBox "File was not Uploaded to Access."
End If
End With[/VBA]
I just set up the FileDialog code from this thread, with a few additions, and I can import various .txt files into an Access table called A2Kmdbs.Quote:
Originally Posted by FrymanTCU
I did create an Import Specification that I refer to in the TransferText statement
Code: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 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 = 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 = -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
MsgBox "Imported Database Names from: " & vrtSelectedItem
Next vrtSelectedItem
Else
End If
End With
'Set the object variable to nothing.
Set fd = Nothing
End Sub
Orange, Your awesome! :bow: :thumb :bow:
Thanks a bunch, now I don't have to cancel my vaction! I'll be sure to bug you next week once I get back!
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.