TransferSpreadsheet : field of Excel file has a period, thus can't map

[derekbeck]

Hi, I have inherited an Access 2007 db. Previously they were importing an excel spreadsheet into a junk table and then running a append query to route the data into the master table. The only reason they used the junk table is because we have more fields in the master than the original source excel file has, and it seems there is no way to append the data directly to the master table.

So, I'm trying to create an "update" macro that will do the import and run the append query. But when I do, I find transferspreadsheet fails because the original excel spreadsheet has a field/column called "tracking/control no." The error I get in Access is that there is no matching "tracking/control#" field. Apparently Access does not like the period at the end, and is replacing it with a #. I tried to change the Access field in the junk table, but it won't allow me to add the period to the end, as it is an invalid character.

So, short of forcing the users to manually change the incoming excel spreadsheet everytime to get rid of the period, how do I get this transferspreadsheet to ignore the period and map it to the field "tracking/control no"?

Thanks,
Derek

 

[Mr. B]

You may not be able to just have Access to ignore the field name. The one thing that you can do is to use VBA to open the spreadsheet and change the column name to what ever you want it to be, close and save the spreadsheet and then use the TransferSpread method and import the data.

Writing the code will take a little while but the actual opening of the spreadsheet and renaming the column, saving the changes and close it will only take a very short time.

 

[derekbeck]

I fear VBA, as it is beyond my experience and understanding. Is there no other way?

Else, is there some VBA snippet out there that achieves this, for which I can simply copy and paste?

Thanks,
Derek

 

[derekbeck]

Alternatively, is it possible via the macro to call upon a saved import, wherein the fields are already properly matched?

 

[derekbeck]

need one more post to be allowed to add my links in the next post below...

 

[derekbeck]

Okay, I solved this by using the RunSavedImportExport. (More info on it at http://office.microsoft.com/en-us/access-help/runsavedimportexport-macro-action-HA010171498.aspx , it is not visible unless you enable "show all actions".) I discovered a flaw in that the parameters dropped down list did not show both of my imports however, per the notes in http://www.accessforums.net/showthread.php/2297-RunSavedImportExport . However, per that thread, if you add a new junk import and save it, it fixes the bug.

So, in summary, I solved my problem using RunSavedImportExport. However, I wish there was some elegant way to use the TransferSpreadsheet command.

While I have a solution, if someone has a simple way to indeed use TransferSpreadsheet and correct the invalid character in the field header of the original excel file, I would be happy to hear it.

Derek