importing spread sheets (automate)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to automate the import of an excel spread sheet that needs to be
loaded monthly. I have written the macro, but continue to get an error that
say's field 'f1' does not exist in destination database. I can't figure this
out for the life of me. The database field names are already titled. The
spreadsheet does not have titles, just raw data. and their are 7 columns in
the spread sheet and 7 columns in the database, please advise.
 
DB,

When you say 'database', I assume you mean Table?

What are the details of your macro?
 
yes that is correct the error message is: field 'f1' doesn't exist in
destination table 'sheet_2'.

Macro Action:
TransferSpreadsheet
OpenTable
Close

Action arguments:

transfer type: import,
spreadsheet type:Microsoft Excel 5-7
table name: sheet_2
file name: C:\Report Writing Project\Data exports\fy06
Has field name: NO
Range: blank

ope
 
DB,

Can you try this again, but put in the .xls filename extension in the
File Name argument of the macro, and also enter as applicable in the
Range argument of the macro.

'sheet_2' is the name of the table in the access database, right?
 
I did as u suggested. However, I keep getting the same error message. I did
notice that there were 7 fields in the table, however 10 fields needed to be
imported. I made the corrections in the table, adding the last three fields
as
field 8, field 9 field 10. After making this correction and taking your
advice, I continue to get the same error message.
 
I am sorry, DB, I do not know what is causing the problem.

As an experiment, could you remove the Sheet_2 table (or temporarily
rename it to something else). That way, the TransferSpreadsheet macro
will be forced to create a new Sheet_2 table during the import process.
See if that works, and if so, compare the design of the table that the
macro creates with the table that you had designed.
 
I experienced a similar problem in the past few weeks and resolved it by
creating an Excel Template from an export of the Access table, including the
filed names as column headings. I do not know how this worked by I imagine it
may have had something to do with the formatting of the cells in the
sreadsheet. I exported the table to Excel formatted, with data, and deleted
the data to create the template. My first attempts without cell headings
failed, it works now with the cell headings identical to the field names.
 
simple,

make sure that the fieldnames in your Excel sheet are the same as the
fieldnames of the table and are in the 1st row of your sheet.
Use Get External data and follow instructions to import teh sheet directly
into the Access-table.

For years this method works perfect, as I receive many sheets of people all
over the country

success

Nanne
 
Back
Top