Access VBA - errors manipulating a linked spreadsheet
Hi
I have an Access database that generates reports in Excel using charts. I had to do this because the Access charting system wasn't working as well. The Excel spreadsheet has tables that are linked to my Access tables. These Excel tables are in turn linked to Pivot Tables in the spreadsheet which are linked to the final charts. I am trying to use VBA to refresh these links and I am getting errors because the Excel spreadsheet cannot be opened at the same time as the Access database. Not sure how to get around this.
I have an Access database that generates reports in Excel using charts. I had to do this because the Access charting system wasn't working as well. The Excel spreadsheet has tables that are linked to my Access tables. These Excel tables are in turn linked to Pivot Tables in the spreadsheet which are linked to the final charts. I am trying to use VBA to refresh these links and I am getting errors because the Excel spreadsheet cannot be opened at the same time as the Access database. Not sure how to get around this.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Si Ball
you'll have to relink excel to look in the new "datastore"
ASKER
Thanks very much. Great answer!
My pleasure. Did it all work ok then?
ASKER
Worked perfectly. I used make table queries, which made tables into the additional database.
I linked my spreadsheet to this database and refreshed from my master database. Appreciate the help.
Was causing a lot of trouble. Thanks very much!
I linked my spreadsheet to this database and refreshed from my master database. Appreciate the help.
Was causing a lot of trouble. Thanks very much!