Office 2007 TransferSpreadsheet Import

[adodson]

FYI - I'm using Office 2007 saving my Excel files in the 2003 format. The
Access database I'm working with is in 2000 format.

I'm attempting to use import functionality of the TransferSpreadsheet macro
as part of an autoexec macro.

To create my Excel document, I'm exporting data from a financial system and
using the import wizard. I'm allowing all of the fields to default to
general format. However, I've also tested using Text & Currency formats.

The settings I'm using for the TransferSpreadsheet macro are:
import
Microsoft Excel 97 - Excel 2003 Workbook
XCSH1
C:\tempdl\xcsh1.xls
No (field names)

My table fields are formatted as Number or Text.

Problem: I can get the table to import with the field names entered, but it
keeps giving me an error about fields not existing if I have the field name
setting set to no.

Reading the help file, I think that I should be able to import without field
names to append a table as long as the fields are in the same order. These
are... any ideas?

 

[Pete D.]

My prefered solution is to use a temp table. Create the table with the
correctly formated fields and link to the spreadsheet. Use a query to
append/update the table as needed. this avoids issues with access guessing
at field formats in excel and places the correct columns in the right
fields. After you have the temp table you can query together what ever you
need. Also check reserved words, spacing and restricted letters/symbols in
field names so your spreadsheet isn't trying to create an invalid field
name.

 

[adodson]

The problem, I guess, is that when you say no header rows, your table fields
have to be titled F1, F2, etc. Once I renamed my table titles to that, I was
able to import without titles in my Excel.

 

[Pete D.]

Actually no that isn't true but glad you got it to work. If you say no
header rows then access creates them, sounds like you were trying to import
to a current existing table in that case fields must match. Anyway glad you
got it. Pete

 

[adodson]

Yes, I was trying to import to an existing table. The 2007 help file makes
it sound like you should be able to do this as long as the fields are in the
same order (last paragraph):

"Spreadsheet data that you append to an existing Access table must be
compatible with the table's structure.

Each field in the spreadsheet must be of the same data type (field data
type: A characteristic of a field that determines what kind of data it can
store. For example, a field whose data type is Text can store data consisting
of either text or number characters, but a Number field can store only
numerical data.) as the corresponding field in the table.
The fields must be in the same order (unless you set the Has Field Names
argument to Yes, in which case the field names in the spreadsheet must match
the field names in the table). "

 

[Pete D.]

Problem is making sure same data type, a cheat is to pad first few rows in
spreadsheet with perfect data. Play with the spreadsheet and you will get
it to work but like I implied, I find it much easier to get it into access
and then query it together. Pete