Automation of importing external data

[Roosters]

I am only just starting to learn Access and was wondering if anyone could help me?
I have data in an Excel file in a defined range that I need to import into an existing table at the press of a button on a form. Now to me this is hard enough but I have a few complicating twists to the task.
The data is entered automatically in the Excel file from another program and the columns have headings that are not valid in Access ie. 'EDMS_REGION.Region desc' so I let it assign names for me ie. 'Field 1'. I need to keep this function as I have been importing the data manually for some time and have keep these Access column headings and all my reports are based on the headings 'Field 1' - 'Field 2' etc.
I hope this doesn't sound too confusing - any thoughts out there?

 

[Guest_imported]

I import data from an excel spreadsheet into an access table, using a button on the form to run a macro group which transfers the contents of the spreadsheet to a table in Access. I did have problems if there was a named column in Excel without a corresponding field in the access table with a similar name.

My way around this was to first run a macro in excel that would insert a new row and create appropriate column headings, identical to the ones in the Access table.

If you want to keep the names assigned by Excel, Field 1 Field 2 etc., then you simply create these fields in the Access table that will recive this data. Presumably you have already done this so I am not sure where it is going wrong.

 

[Quehay]

I can't remember if you can run Excel macros from Access or not. You can create the necessary macros in Excel and then parse the code to create a new routine. The suggestion above to rename your columns is good. Another option would be to have an append query that routes the columns from "Field 3" to "AccTable.FieldName" --as long as the Excel sheet has a stable layout this will work.

Overall this will be a tenuous operation if you don't have VBA chops--but this isn't a daunting obstacle either. There are some good resources in the MSDN help--look up "Automation."

Maybe the way to cut Gordian knot is just to link the XL sheet as a table. This will make the current info available. If your table is comprehensive (and the sheet isn't) you can do the append query from sheet to table.