Importing an Excel spreadsheet

[Timothy Millar]

I am a little new to Access so please forgive me if I sound new.

I have a DB that I would like to set up a comman button to import an Excel
spreadsheet but I want the End User to be able to select the location and
name of the Xecel document they want to import. The spreadsheet they will be
importing will be properly formated so as to cause no problems.

Thank you

 

[Dale Fye]

Take a look at the code and descriptions at the following URL

http://www.mvps.org/access/api/api0001.htm

HTH
Dale

 

[PJFry]

Timothy,

After you creat a command button, go to the Property Sheet and select the
Event tab. Under the Event tab will be an action called 'On Click'. Select
the On Click event and you will see three periods (...) at the end of the
line. Click on the those and the Event Builder window will pop-up. Select
Code Builder. Paste this code in between the Sub and End Sub lines:

Dim sFileName As String

sFileName = "C:\FilePath\FileName.xlsx"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tNewTable",
sFileName, True

'***********************************************

Change the sFileName to equal the place where your excel file is stored.
Next change the acSpreadsheetTypeExcel12 to match your current version of
excel. This one is for 2007. acSpreadsheetTypeExcel9 would work for 2003 (I
think).

Change tNewTable to match the name of the table the data in going into.
Leave the quotes.

The word True after sFileName refers to the column headers. If your file
has headers you want True. If it does not, change that to False.

Let me know if you have any problems.

PJ

 

[Ken Snell MVP]

Browse to a single EXCEL File and Import Data from that EXCEL File via
TransferSpreadsheet
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#ImpBrowseFile