Name not valid error on linked tables refresh

[bren1519]

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 spreadhsheets 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

 

[genomon]

Not a file name, probably a field name. Make sure that all the field names do not contain crazy characters, and are not VBA reserved words.

Cogito eggo sum – I think, therefore I am a waffle.