By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,327 Members | 1,805 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,327 IT Pros & Developers. It's quick & easy.

Allow user to choose what file to import

P: 9
Is there a way to have the user choose what file to import, but set everything else up in VBA?

The command button that would start the import would have something like this --

Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferSpreadsheet acImport,acSpreadsheetTypeExcel9, "tblMAIN",???,True
-- with the ??? indicating where you would typically put the path and name of the spreadsheet. However, the spreadsheet that will be imported will have a variable name including the date (such as "IMPORT080807.xls").

Any thoughts on how to do this?

Thanks!
Aug 8 '07 #1
Share this Question
Share on Google+
9 Replies


Rabbit
Expert Mod 10K+
P: 12,392
You could use the filedialog class to open up a Select File Dialog where they navigate to the file they want.
Aug 8 '07 #2

P: 4
Here is a way you can have a browser window pop up and they can select the file, regardless of the name or filepath.

Function GetFile()
'Declare a variable to contain FileDialog
Dim sMyPath As FileDialog
'Declare a variable to contain the path
Dim sPath As Variant


DoCmd.SetWarnings False
DoCmd.Hourglass True
' Set up the File Dialog.

Set sMyPath = Application.FileDialog(msoFileDialogFilePicker)

With sMyPath

' Allow users to not make multiple selections in dialog box
.AllowMultiSelect = False

' Set the title of the dialog box.
.Title = "Select your File"

' Clear out the current filters, and add your own.
.Filters.Add "All Files", "*.*"

'Set the text of the button Caption
'.strButtonCaption = "Get File"

'Use the Show method to display the File Picker dialog box and return the user's action.
'The user pressed the action button.
If .Show = -1 Then
For Each sPath In .SelectedItems

'import into primary table
DoCmd.TransferSpreadsheet acImport, "tblName", "Equipment", sPath, True
Next sPath
Else
'Show if Canceled is selected in a message box
sPath = "No File Selected to Import."
MsgBox sPath
End If

End With
DoCmd.SetWarnings True
DoCmd.Hourglass False


End Function

If you are setting up a form, you can direct a command button to this function and the user can import the file.
Aug 8 '07 #3

P: 9
It won't compile. It freezes on "Dim sMyPath As FileDialog". FileDialog is not an option. Do I need to reference a specific library? I'm using Access 2000 with VB 6.5.

Thanks.
Aug 8 '07 #4

Rabbit
Expert Mod 10K+
P: 12,392
Access 2000... I don't think FileDialog came with Access 2000. It started with Office XP and up.
Aug 8 '07 #5

P: 9
Thanks Sysdupe123 and Rabbit.

I was bummed to hear about FileDialog not being available on A2K.

I ended up using something like this...

Expand|Select|Wrap|Line Numbers
  1. Dim strDate as string
  2. strDate = InputBox("What is the date on the import file?", , "mm-dd-yyyy")
  3.  
  4. DoCmd.TransferSpreadsheet acImport,acSpreadsheetTypeExcel9, "tblMAIN","IMPORT_" & strDate & ".xls",True
This got the job done.

Thanks again!
Aug 10 '07 #6

P: 47
Thanks Sysdupe123 and Rabbit.

I was bummed to hear about FileDialog not being available on A2K.

I ended up using something like this...

Expand|Select|Wrap|Line Numbers
  1. Dim strDate as string
  2. strDate = InputBox("What is the date on the import file?", , "mm-dd-yyyy")
  3.  
  4. DoCmd.TransferSpreadsheet acImport,acSpreadsheetTypeExcel9, "tblMAIN","IMPORT_" & strDate & ".xls",True
This got the job done.

Thanks again!
Sorry about this post. It was double posted so I removed this one.
Aug 10 '07 #7

P: 47
Thanks Sysdupe123 and Rabbit.

I was bummed to hear about FileDialog not being available on A2K.

I ended up using something like this...

Expand|Select|Wrap|Line Numbers
  1. Dim strDate as string
  2. strDate = InputBox("What is the date on the import file?", , "mm-dd-yyyy")
  3.  
  4. DoCmd.TransferSpreadsheet acImport,acSpreadsheetTypeExcel9, "tblMAIN","IMPORT_" & strDate & ".xls",True
This got the job done.

Thanks again!
You could use the file dialog if you set a reference to Microsoft Office 11 Object library (11 or Higher). You can do this in your vbscript editor by clicking tools/references and then find the object library from the list. But it looks like you figured something else out. Good Luck.
Aug 10 '07 #8

P: 9
Thanks for the idea. I actually just tried setting the reference to the Microsoft Office 11.0 Object Library (as a learning experience :) and it still can't find it when I debug.
Aug 10 '07 #9

P: 2
I'm having the same kind of issue I have the file on the desktop of the users computer it's the same place that they will have their mini database.
It seems to not be able to find the file it give me the error msg I have put in everytime. If I need the whole path the login of the user has to be entered by them. Thats the reason for the inputbox section.
Expand|Select|Wrap|Line Numbers
  1. Function Importfile()
  2.  
  3. Dim login As String
  4. On Error GoTo Err_Importfile
  5. 'login = InputBox("What is your LAN ID?")
  6.  
  7. DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tracker", "trackerimport.xls", True
  8.  
  9. Exit_Importfile:
  10.     Exit Function
  11.  
  12. Err_Importfile:
  13.     MsgBox "Tracker File is not found. Please save sales tracker export to desktop and as trackerimport.xls and try importing again"
  14.     Resume Exit_Importfile
  15. End Function
  16.  
Aug 17 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.