By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,948 Members | 664 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,948 IT Pros & Developers. It's quick & easy.

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

P: 11
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
Sep 5 '08 #1
Share this Question
Share on Google+
5 Replies


NeoPa
Expert Mod 15k+
P: 31,342
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?
Sep 5 '08 #2

P: 11
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.
Sep 8 '08 #3

NeoPa
Expert Mod 15k+
P: 31,342
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.
Sep 8 '08 #4

P: 11
Thank you for your help!
Sep 24 '08 #5

NeoPa
Expert Mod 15k+
P: 31,342
No worries Mary Ann.

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

Welcome to Bytes!
Sep 24 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.