Changing field names or setting field names while importing a range of cells from Excel to Access

[Ralph]

I'm trying to import a range of cells from an Excel spreadsheet into a
table in access. The first row contains column labels, but I cannot
use those as my field names, both because of their format (number,
space, text) and because I've got a whole bunch of similar tables and
I need to have consistant field names in these tables for some data
manipulation I'm doing in VBA.

I've used the following statement to bring the data in with the column
headings as field names:

DoCmd.TransferSpreadsheet acImport, 8, "Mwftest",
"c:\sampleratesheet", True, "page2!a15:d23"

Or I can modify it to create default feild names:

.. . . False, "page2!a16:d23"

However, I don't know how to specify what those field names will be.
I've been working in Access for years, but I'm just now starting to
learn VBA. I've been searching through newsgroup threads and think I
might have found some ways to work around this problem (e.g., rename
the fields in a query). However, that just seems like a sloppier way
to go. Can anyone give me some sample code for this?

[Squirrel]

Hi,

If this is a one-time process, could you import the data stating that the
first row does not contain fieldnames, then Access will assign Field1,
Field2, Field3 etc. Open your new table, delete the first row (the Excel
column headings) and then rename the fieldnames to your liking?

HTH Linda

"Ralph" <ra******@arrowheadcu.org> wrote in message
news:1d**************************@posting.google.c om...

I'm trying to import a range of cells from an Excel spreadsheet into a
table in access. The first row contains column labels, but I cannot
use those as my field names, both because of their format (number,
space, text) and because I've got a whole bunch of similar tables and
I need to have consistant field names in these tables for some data
manipulation I'm doing in VBA.

I've used the following statement to bring the data in with the column
headings as field names:

DoCmd.TransferSpreadsheet acImport, 8, "Mwftest",
"c:\sampleratesheet", True, "page2!a15:d23"

Or I can modify it to create default feild names:

. . . False, "page2!a16:d23"

However, I don't know how to specify what those field names will be.
I've been working in Access for years, but I'm just now starting to
learn VBA. I've been searching through newsgroup threads and think I
might have found some ways to work around this problem (e.g., rename
the fields in a query). However, that just seems like a sloppier way
to go. Can anyone give me some sample code for this?

[Ralph Ambrose]

Actually, I need to do this a dozen times every day. Also, I'm trying
to automate this for other users who don't understand the process at
all, so it cannot be a manual process at all.

You're basically describing the second example I gave, ".. . . False,
"page2!a16:d23". I should have actually used a14, one less line to
avoid the headings. This example returns the Field1, Field2, etc. that
you described- That's what I was referring to as "default field names".
I'm looking for a way to set these field names in VBA.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!