Import Excel file to Access

[tnemom]

I have the following code that transfers data from an excel file into a table in Access. What I would like to do is edit the code so that the user can select which Excel file to transfer from. Data changes from year to year and copies of the same file are saved in different locations based upon the year.

Option Compare Database

Private Sub Command0_Click()
strTableName = InputBox("Enter table name:", "TABLE NAME")
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTableName, "O:\PWM_Shared_Files\Stations Estimates\Sent Complete Estimates\2008\Zones more than one\2008 PM Unit Prices\2008 UnitPrices June 20 07.xls", True

End Sub

Thanks,
TNEMOM

 

[Joe4]

You have a few options.

You can set up another InputBox where the user can type in the file name & path, or you can set-up a Open File Dialog box. Unfortunately, it is not nearly as easy to do that in Access as it is in Excel. There is some esxtensive VBA code you need to copy. The good news is, you really don't need to understand or edit it, just use it.

Here is one link to that code:
http://www.mrexcel.com/board2/viewtopic.php?t=85521&highlight=openfile+dialog+access

 

[tnemom]

So I just put that code into mine and it should work? I am not familiar with Access at all and that code just confuses the heck out of me. First off...what do I do with it? Do I just copy and paste it?

 

[Joe4]

Just create a new blank module and paste all the code there.

For information on how to use the code, check out my posts to this thread:
http://www.mrexcel.com/board2/viewtopic.php?t=106991&highlight=browse+file

My second to last reply tells you how to use the code to bring up the File Dialog box through a command button.

The last thread shows you how to edit one line of code to tell it what kind of files you will be browsing for.

If you also Search this board for "browse and file" and limit it to the Access forum, you will see many other threads which may help.