[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 515
  • Last Modified:

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.

0
RSarumi
Asked:
RSarumi
  • 5
  • 4
1 Solution
 
LucasCommented:
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
 
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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
 
RSarumiAuthor Commented:
Clotha:  It worked!....you are what the people call....The Man!!!

0
 
ClothahumpCommented:
Glad I could help you!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now