Access/VBA: Automated Import

I'm in the middle of creating an Access DB.  The table data will be imported from various Excel Spreadsheets.  I'd like to automate the imports of these spreadsheets.  I'm aware that I'll need to use the TransferSpreadsheet method, however, the imports will be ongoing, and I'd like the use to have control over which spreadsheets are imported and when.

That is, I'd like to, on a button's onClick event, have a File Open dialog box open, allow the user to select the file name of the Excel spreadsheet to be imported, and then have that file name passed back into the TransferSpreadsheet method as a parameter.  Any ideas on how I can do so?

Thanks.

RSarumiAsked:
Who is Participating?
 
ClothahumpCommented:
Change the statement to end after the True parameter

DoCmd.TransferSpreadsheet (acImport), acSpreadsheetTypeExcel9, "payors_test", sPath, True

You have a string value there on the end of yours; that's the UseOA parameter and should be a boolean.
0
 
LucasMS Dynamics DeveloperCommented:
This line:     DoCmd.RunCommand acCmdImport

Will open up a dialog box that allows the user to select which file they want to import.
0
 
ClothahumpCommented:
Try something like this:

Dim dlgOpen As FileDialog
Dim sPath As String
Set dlgOpen = Application.FileDialog(msoFileDialogOpen)
dlgOpen.Show
sPath = dlgOpen.SelectedItems(1)



sPath should now have the path to the desired source file.  Plug it into your TransferSpreadsheet command and away you go.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
RSarumiAuthor Commented:
Clothahump:  When i use that syntax, I get the error message:  User-defined type not defined.

Maybe I haven't referenced the proper library?  What library is the FIleDialog object in?

Thanks.
0
 
ClothahumpCommented:
You have to set a reference to the Office Object library, v11 or v12 whichever you have.

Sorry, I should have mentioned that.
0
 
RSarumiAuthor Commented:
I'm not getting the runtime error 2498: An expression you entered is the wrond data type for one of the arguments.

Below is my code:

Dim sPath As String
Set dlgOpen = Application.FileDialog(msoFileDialogOpen)
dlgOpen.Show
sPath = dlgOpen.SelectedItems(1)

DoCmd.TransferSpreadsheet (acImport), acSpreadsheetTypeExcel9, "payors_test", sPath, True, , ""

Any ideas?  Thanks!
0
 
ClothahumpCommented:
I'm assuming you included the line
Dim dlgOpen As FileDialog


Also, try taking the parentheses off of acImport.  Access can sometimes be really squirrely about things like that.
0
 
RSarumiAuthor Commented:
Clotha:  Yes, I did.  And I removed the parenthesis from acImport, no luck....
0
 
RSarumiAuthor Commented:
Clotha:  It worked!....you are what the people call....The Man!!!

0
 
ClothahumpCommented:
Glad I could help you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.