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:
fsADOConnectExcel.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).
9
