changing Excel column names from Access

[McDimwitty]

I have been trying to import an Excel spreadsheet into an Access table.

I have no say in how the XLS file is formatted. And the users of my Access application will be importing XLS files on a daily basis. The XLS file's header names are usually 2, 3 or even 4 words long.


I have read that to import a XLS into a table, the table's field names have to be the same as the spreadsheet column / header names.

I was hoping to change the XLS header names from within Access.

There is a new XLS file every day, so I am not able to run an Excel macro from Access.

How would I go about it? I have worked on it for several hours with NO success.

If you have a way to do it other than the way I described, please tell me.

=-= Mr McDimwitty =-=

 

[McDimwitty]

imported spreadsheet - table all wacky

I imported a spreadsheet into an access table, but when I open up the table, only the first two columns are there. The first is the primary key - autonumber. The rest of the fields are blank.

Is there another way to get the data into Access. I have a big deadline at work.

Is it possible to have Access via automation have Excel convert the xls into a csv? I have a working sub that imports csv files already.

 

[boblarson]

What is your spreadsheet and how have you been trying to import it?

 

[boblarson]

I think you posted a semi duplicate post. Please try to avoid doing that as it gets hard to want to answer if someone else answered in the other post. I posted a question to you there, which you basically answered here, so I will answer and probably merge the two posts.

What I would do is to import the spreadsheet first to it's own table and then use Append Queries to move the data to the table you want it in. That way you are not constrained by field names.

 

[YevS]

So you need to import data from Excel into Access and you have a different format excel file every time, what about your access? Is that also different or does it stay the same?

are there common field names between the 2? Is it just the names that are different or are the columns in a different order too?

Maybe if you give an example of the access table and the excel spreadhseet you are trying to get data from we could help you out better.