Import Excel SS Header Issue

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've got an Excel SS with a column header as Fin. Arr.

This denotes 1 column - not 2 separate columns.

In the TransferSpreadsheet macro, Access doesn't like the periods in the
column header. Is there anyway I can get Access to accomodate the header as
is? I don't really want to manually remove the periods before I import the
spreadsheet.
 
Kirk

I don't know if prefixing that column header with an apostrophe would make
any difference, but if you're doing that much work, why not fix the periods?

Access is telling you it doesn't handle periods in the title of the column.
Is there a reason not to fix this? I suppose if you'd like to keep the
periods in your Excel spreadsheets, you could consider inserting another row
beneath the periods row, and use no periods in that row. Name the range and
import it.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Jeff,

You gave me the answer I needed - basically Access can't deal with the
periods. I wish I could just get rid of them at the source, but the source
of the data is a query of a Corporate database. I either have to modify the
query (users can only run, not create or modify queries) to strip out the
periods, or modify the field name in the database - either of which would
involve the Corporate IT department. I'm just a meek little consultant in a
$70 billion organization - IT won't change field names or modify queries just
because I need it. I don't even want to wade through the paperwork and
approvals required to even ask them - I probably won't be here that long!
:) :)
 
Kirk

Also worth a try/test...

Import the Excel data as is.

Create a query that "fixes" (e.g., concatenates two fields) what the periods
break.

Use a "permanent" table, and append/update records there, from the imported
(and "broken") table, via the previously-described query.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Back
Top