Import Excel check field names Please Help

[cynthia]

I am importing an excel file where the field names do not match my table
field names. I have a matrix in access that lets me know what the match is.
I would like to create the import temp table through vb code making all
fields text, then import the excel file to that temp table.

Here in lies my problem. I would really like to check that the excel file
has the correct field names.

For example in my access table length is my field name. Depending on the
client the excel equivalent could be len(for client1), dimension(for
client2), or numlen(for client3). I would have an import button where the
user selects which client he is importing from, Let's say Client3, then the
vb code would check the matrix (filled in by users) and create a temp table
with the field of numlen, etc for the rest of the fields for that particular
client. I can then easily import the data to that table.

What if the field name from the client was mistyped i.e. nmlen. I would
like to check the excel field names to make sure I have a match in my temp
table and let the user know if there is an issue before I do the import. Is
there anyway to do this?

I do not want to do a creation of the table using the transfer spreadsheet,
because at the start alot of records would be blank and the few that are text
would not import since access assigns field values number if the first so
many records are blank. I thought about creating a second table using the
transfer spreadsheet, but this would be taxing on the system since I have 75
possible fields for some clients and 2,000 or so records.

For some reason I do not get emailed when a response is made, so I will
check this post every few hours. Thank you for your help.

 

[PieterLinden via AccessMonster.com]

Sounds to me like you're making this a lot harder than it should be.
Why not create an import specification for each client and then import into
the same table? If absolutely necessary, import into the same temporary
table each time with all the fields you may need and that are defined as text
(if necessary). Then use queries to parse the data out to the final table?

Depending on how the spreadsheets are set up (if you control the template),
you can import from a named range on the spreadsheet...)

If you tell Access not to use the field names, they're totally irrelevant at
that point. But then you would need to know somehow which data should be
appended to which field in your table.

 

[cynthia]

To create an import specification I need to know the client fields. These
can change, the client field may be one name, at the beginning of a project
and change on down the road. Because of this I have set up a matix form
where the user tells me what the client field is per my field. I have the
export completed. It reads the matrix form and through code creates the
query myfield as clientfield, etc.

On the import I have created the temp table through vb code the same way,
but thought it would be nice to make sure the fields being imported were in
the temp table and if not let the user know. If I cannot do this, it will
rest of the users to make sure they are named correclty.