Importing Excel with full stops in column heading

[Keredw]

Software we use produces an Excel spreadsheet that is manually imported into Access 2003. Up until now this was OK. I then employed the TransferSpreads heet method to automate the process. Some of the column headings now has a hash, so instead of being [End Dt] it was now [End Dt#] which didn't please the queries at all. Found that some headings had full stops in them (Periods) so [End Dt.] was [End Dt] if imported manually but [End Dt#] if the transfer method was used.
Now I could do an APPEND query and copy the data across after importing to an intermediate table but it would be more elegant if I could control the full stop to # conversion. Or indeed include the full stop to # conversion in the manual method??
I am using the On_Click function on a button on a form to do the import.

[zmbd]

Please post your code. I've never seen this happen before and I've used the transfer method for many years.
Please format you code using the [CODE/] formatting button
thnx

[edit] Let me qualify this a tad... I've never seen the data changed before as described. After reading the post a 2nd time, I see that OP is refering to the headers, that then become field names. Periods are not allowed in field names nor are a lot of other special chactors without a lot of massaging.
* Access 2007 reserved words and symbols
* AllenBrowne- Problem names and reserved words in Access

[Keredw]

Thank you for your reply. There is nothing special about the code, it is just the standard one line code using the TransferSpreads heet method. I have actually found others with a similar problem but in reverse. They had a Hash in the Excel Header name and the import converted it to a period?? What I have done as a work around is to run a VBA Delete query to empty table1 and table2. Use the Transferspreads heet to import data (with hash in column heading) into Table2 then use a VBA Append query to move the data across to Table1 and linking headings such as [End Dt#] with [End Dt].
This works fine and is compltely transparent to the button clicker.
Try it for yourself, create a simple Excel spreadsheet with a period in one header and use the manual method and then the VBA method.
It is just the way it works.
Thanks again for your interest.

[zmbd]

The manual method has a column linking step in it, hence why it will work. The transfersheet method is making a best guess and when it encounters an illegal character it will either fail or attempt a temporary fix.

A few options that I've ran across for VBA:

- Do as you have done with the temporary table. I do this anyway as often the files I import via excel will have invalid data in the cells. This tends to be the most painless method for smaller transfers - and the end user normally doesn't see too much drag... just turn on the elevator music for them :-) .

- There is a way to run a saved import method in ACC2007. Basically, do the import method once, save it, and then call the stored import method via VBA (sorry, forgot about this one, I don't tend to save these for production applications. Bytes.com: How can I run a saved Export in VBA ? This should also work for ACC2010. One thing, I believe the saved import also saves the absolute filename/path so if you are dealing with multiple file names this may not be the best option.

- Use automation to open the spreadsheet and fix the header row... or for that fact, you could just step thru the sheet and read each cell value. (I've done this... not that bad, just time consuming and a frustration for the user).

- Open a record set on the named range in the worksheet: fsADOConnectExc el.pdf This is pretty slick stuff.

HOWEVER, with all of these methods the best solution I've found in-house, was to go thru all of our various Excel Workbooks and remove all of the invalid characters. I have a VBA code that simply takes the list of special characters and forces a search and replace for each of them in turn with the underscore. I then sent out an email with the list of prohibited characters to the staff and asked them not to use these in "header names" for any file that would be imported to the database(s).

[Keredw]

Thank you again for taking the time to reply with such a comprehensive answer. Unfortunately I am using software at work and I wish to have it work for others so I need the solution that is transparent to the user. We will migrate to 2007 in the future but it is 2003 for now. I have Office 2013 at home.
Thanks again.

[zmbd]

IMHO: avoid OFFICE2007!
Move directly to Office2010 or 2013

I've used 2007 at my inlaws business, I found it clunky and hard to use. When we moved to 2010 at in the lab, I upgraded my stuff at home to 2010.

My only gripe about 2010 is the enforcement to use Sharepoint as the web-interface; however, due to another thread here, I've started looking at .asp webpages...

however, I drift off topic now.

[Keredw]

For information and completeness, I have abandoned the Transferspreads heet method of bringing the table into Access. After sending out 66 reports on Friday one of my colleques pointed out that there was a wrong date in one of the 4,000 records. The SAP file was good. The exported Excel file was good. The manually imported file was good but the Transferspreads heet method put the wrong date in this particular record (was 12/8/2013 but became 17/6/2013) most of the other data looked OK but I didn't have time to investigate. Since it is only one line of code, there is not much to investigate. Is it due to the Headers? I hope so otherwise it would be the method and it would have been noticed by others. I had to send out emails to everyone saying disregard the worklists... Is this a case of 'a code too far'?

[zmbd]

Keredw:
Personally, I would look at that particular record.

I've used this method to export/import tens-of-thousands of records over the years and usually the only time there was a transcription error was on the import due to the mis-entry or mis-formatting in the imported Excel file. I've yet to have an exported data error.

If you would like to investigate this further, I ask that you
start a new thread. It is an intriguing question given that not only was the date wrong but also changed from the normal "dd/mm/yyyy" format to what appears to be "dd/mm/yyyy".

[Keredw]

OK its embarrassment time again..... Remember that one line of code "What can go wrong?"...
well I had

  DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblGWOSData1", "C:\worklist.xls", True

which I must have done during the developement phase when I should have had...

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblGWOSData1", varFile, True

In other words I was bringing in the same old data instead of the data I was selecting.
Thanks for your assistance and the chaps at Microsoft can rest easy again...

Regards, red faced Keredw

[zmbd]

Wish I had a Dollar for everytime I've done something like that... but then I'd be a rich man