11-04-09, 14:50 #1Registered User
- Join Date
- Nov 2009
Unanswered: Name not valid error on linked table refresh
I am trying to automate some financial reporting for one of our staff members. He receives 48 emails every quarter with an excel file attached with that division's figures. He was manipulating the data so every one was in exactly the same cells then copying and pasting to a workbook which had links to the summary.
What I have done is link the 48 spreadsheets to an access database and queried on the results. My idea is that when he receives the email all he has to do is save as "*.xls" and overwrite the current file. Then in Access he refreshes the linked tables and runs the query. No manipulation of the files is needed and it is (theoretically a five minute procedure instead of a five day procedure).
Everything works with the sample excel files but when I "save as" from the email attached file and then try to refresh the linked tables in access I get the following error message:
****$ is not a valid name. Make sure it does not include invalid characters...
I have not changed the file name as I am overwriting the sample excel file that work fine. If I open the email attachment, copy, and paste the data into the existing sheet and resave it works fine but not the route I was hoping for!
Sorry for the long winded message but I was trying to be clear on what I am attempting to do . Thx for any help! Brenda
11-04-09, 17:01 #2Moderator
Provided Answers: 14
- Join Date
- Mar 2009
Don't use links to Excel files, use the DoCmd.TransferSpreadsheet method instead.Have a nice day!
11-04-09, 19:56 #3Moderator
- Join Date
- Dec 2004
- Madison, WI
I've seen something similar with the ***$ is not a valid name (with the 2007 version of Excel.) Mainly the $.
I'm not sure what is exactly going on but I think it has something to do with the worksheet name itself in the excel file.
Not sure if this will help but I do have a routine that works quite nicely for exporting tables to an excel file if you're interested. It can be found here: http://www.dbforums.com/6390529-post84.html
Last edited by pkstormy; 11-04-09 at 20:00.Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)