Importing from Excel

[Susan]

I am trying to import from Excel 2000 into Access 2000
into an existing table -- BUT the option to import into
the existing table in grayed out. Any ideas why? I tried
to import into an empty table and then I put a dummy
record in -- no luck.

If I let Access create a new table and then copy/paste it
into my table, it works -- but this is not the way I want
to go.

I want to eventually automate this with a macro but need
to have the import into an existing table working first.

Any help is greatly appreciated!

 

[Joe Fallon]

Excel and Access have many little problems when used together.
It is a lot simpler if you follwo this strategy:
Use TransferSpreadsheet to import to the table of your choice.
Even then I wouldn't put the data into the real table!
I always use a temp table for this purpose. It can be called a staging
table.
Then when the data is imported to it, I can run an append query and massage
the data as I move it to the real table.
I can format fields, join them together, etc.

 

[Susan]

Thanks -- but this is for running reports on request. I
had wanted to automate the process so the staff can run
them -- but this way I will have to do it. Any other
solutions? I know that when I first did testing on this
about 3 wks ago, I COULD import into an existing table --
but then the grayness now appears on the dialog box. I
can't understand why it appears now (same pc, etc.) and it
didnt' then.

I had created a temporary table only for this process --
and the first time I did it the data imported fine.
Thanks for your help!

 

[Joe Fallon]

I just tried it using Access 2002 and the option for importing to an
existing table is not grayed out.
I imported an Excel file to a New table, deleted the data and then imported
the data to the existing table.
It worked fine.

1. Using TransferSpreadsheet in code should allow you to automate the
process and use an existing table.
(Even if your manual steps have a grayed out option for existing tables.)

2. Create a new .mdb and re-test using the same steps I outlined above.
Does it work?

3. Check MSKB for this error.