Automating Excel import

  • Thread starter Thread starter Cesar Zapata
  • Start date Start date
C

Cesar Zapata

HI,

I have a imported an excel worksheet to the table TblItems manually and it
worked fine. My question is . How can I make access reimport the worksheet
automatically on opening? and how can I make it to only import items that
are not already in the table?



any help is appreciated.

Thanks,
CZ
 
Hi Cesar,

To import an Excel file you can create a Macro and use the command
TransferSpreadsheet.
To suppress Access messages use command SetWarnings -> Off
Unfortunately it is not possible during the import just only to add new
records or lines. But what you can do is, import the Excel file to a
different table, then have an Unmatched Query (look at the query wizards) and
use an Append Query to add the result of the Unmatched Query to the already
formated final table TblItems.

To automatically start the macro on opening the database there are 2
possibilities:
1. call the macro : Autoexec
2. from Main Menue : Tools -> Startup and enter the macro name in the
Application title

If you press the 'Shift' Key while opening the database the Autoexec macro
and Startup settings will not be executed.

Hope this helps,
Bernd
 
To import an Excel file you can create a Macro and use the command
TransferSpreadsheet.
Unfortunately it is not possible during the import just only to add new
records or lines. But what you can do is, import the Excel file to a
different table, then have an Unmatched Query (look at the query wizards) and
use an Append Query to add the result of the Unmatched Query to the already
formated final table TblItems.
Click to expand...

Here are some of my basic principles:

- If a database operation cannot be achieved or at least expressed as
sql then there is very likely a design flaw (in the schema, the
approach taken or the DBMS product <g>).

- There is rarely any need for a temp table; a derived table or
subquery usually suffices.

- Never trust a wizard to write code!

I don't speak the language of the MS Access wizardry but I think
something like this is required:

INSERT INTO MyTable
(MyKeyCol, MyDataCol)
SELECT
T2.MyKeyCol, T2.MyDataCol
FROM
MyTable T1
RIGHT JOIN
[Excel 8.0;HDR=Yes;Database=C:\MyWorkbook.xls;].[MySheet$] T2
ON T1.MyKeyCol=T2.MyKeyCol
WHERE
T1.MyKeyCol IS NULL
;

Jamie.

--
 
Back
Top