Link to home
Create AccountLog in
Avatar of mruys
mruys

asked on

Import Excel files into existing Access table

I am struggling to import excel files into an existing Access table. I have designed a database to automate certain recurring (once a month) processes within the company. Currently it is working fine for one month, but I would like to have it working for several periods and further automate the upload from certain Excel files.

So my first problem is: how do I import Excel-files in Access with a click on the button into an existing table?
Second problem is: when I import Excel-files in Access, is it possible to add a field that is not included like the month (e.g.: when I import colums A-G from an Excel-file I assign them to certain columns from an existing table, but the first column from the table in Access should be populated at the same time with the month (ideally this month is chosen from a list at time of import).

Already many thanks for all your help
Avatar of tbsgadi
tbsgadi
Flag of Israel image

Hi mruys,

use the TransferSpreadsheet Method or Action
http://msdn2.microsoft.com/en-us/library/aa193071(office.10).aspx

Good Luck!

Gary
If your worksheet is set up like data, ie columns and rows, link your spreadsheet to your Access file and then use a query to import the data to an existing spreadsheet data to your table. In the query choose any extra that you would like, like month.

EA
Avatar of Rey Obrero (Capricorn1)

this command line will import the whole content of sheet3

  DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
     "TestTable", "C:\Test.XLS", True, "Sheet3!"


<but the first column from the table in Access should be populated at the same time with the month>

you have to run an update query to populate the  first column with month, after importing the excel file

currentdb.execute "Update TestTable set FirstColumnname=format(Date(),'mmmm')"



<when I import colums A-G from an Excel-file I assign them to certain columns from an existing table,>

better use a temp table, then run an append query .
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
     "TempTable", "C:\Test.XLS", True, "Sheet3!"

select the columns you want to append to the Destination table..

ASKER CERTIFIED SOLUTION
Avatar of Natchiket
Natchiket
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account