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!