Import Excel data with acImport

[djleggins]

Dear all,

I am a newbie in Access 2007. I discovered the method

DoCmd.TransferSpreadsheet acImport, _acSpreadsheetTypeExcel2007, _"ImportTest", _"Z:1996.xls", _True, _"P:Q"

So how do I embed this. I would like to have a button in a form "Import Data" which activates the Import. Then, wenn the import is successfully completed, there should be a confirmation.

Additionally, I do not want that the import overwrites data, it should just add the new data. The Excel sheet will always remain in the same structure. I will just add some data on a daily basis.



Thanks.

[hertfordkc]

Create the button on a form. Then go to the property sheet for the button, go to the OnClick event and click on the ellipsis (,,,) at the end of the line. Select VBA code
and the basic onclick subroutine will appear. Insert your code and you should be able to do the import. Where did the underscores in your code come from? They will probably create a compile error.

Dear all,

I am a newbie in Access 2007. I discovered the method

DoCmd.TransferSpreadsheet acImport, _acSpreadsheetTypeExcel2007, _"ImportTest", _"Z:1996.xls", _True, _"P:Q"

So how do I embed this. I would like to have a button in a form "Import Data" which activates the Import. Then, wenn the import is successfully completed, there should be a confirmation.

Additionally, I do not want that the import overwrites data, it should just add the new data. The Excel sheet will always remain in the same structure. I will just add some data on a daily basis.

You don't specify what distinguishes new data. Is it date, record number...?
Once you can create a query that selects the new data, then it is a matter of creating an append query to add it to the permanent table.
Thanks.

[Access_Blaster]

For more ideas check out Ken Snell's page.
http://www.accessmvp.com/KDSnell/EXCEL_Import.htm