Macro /VBA that imports excel file to table using First row contains header function

[maryanncanor]

Hi, I have an input excel file that needs to be imported to Table1. However, the fieldnames or header in the excel file doesnt match the fieldnames in the database. The fieldnames in the excel file have spaces.
For example: Middle Name (field name in the Excel)
MiddleName (fieldname in the database)

My idea is to import the excel file in the table with First Row contains header option, so I dont have to rename the fields that was given by

Obviously, Ms access doesnt allow field names with space.

Is there a macro or VBA that can import excel file to the table with First row contain s header option so I dont have to rename the input file that I will be getting every month?

Thank you so much!

I am using MS Access 2003

[NeoPa]

In fact Access CAN handle spaces in field names. It's not generally wise to use them, but there are circumstances where their use can be sensible.

Is your problem solved simply by designing the Access table to match?

[maryanncanor]

sorry, Access can allow fielnames to have spaces. However, it doesnt allow fieldnames with period.

I am going to receive access files regularly that may have INVALID fieldnames.

Is is possible to import only the contents of an excel to ms access database?.

So, the range will start at A2.

[NeoPa]

It's lucky I checked, as I thought there wasn't any way (straightforwardly) within the DoCmd.TransferSpreadsheet() function.

However, look at the Range parameter and you will see that it can be done.

F1 on the DoCmd.TransferSpreadsheet code for the Help on this.

[maryanncanor]

Thank you for your help!

[NeoPa]

No worries Mary Ann.

I learnt something here too (always a bonus) :)

Welcome to Bytes!