Opening an Excel Spread sheet from within Access which links back to a table in Access?

[andyfive]

Hello All,

I have an Access Database with a table called Customers. I have a series of macros set up in Access, one of which needs to open an Excel spreadsheet (via some Vba code) to perform some formatting. The problem is, the spreadsheet has a link back to the Customers Table in the Access Database (the same one the macros are running from).

The Spreadsheet opens OK but the linked data is not being updated. I think that this is probably because the Access Database is locked.

Is there some way I can get this to work, by perhaps temporarily unlocking the Access Database/Customer Table? Or perhaps another way around this?

Many Thanks.

 

[xenou]

I have a series of macros set up in Access, one of which needs to open an Excel spreadsheet (via some Vba code) to perform some formatting.

Honestly, it would be best not to worry about formatting. If formatting must be done, do it on the Excel side alone, at a time when it's not connected to Access. I personally avoid links to Excel spreadsheets as much as possible (i.e., always) since Access doesn't play well with Excel this way. It's been a while but my recollection is that the workbook is locked (read-only) while connected to Access, which pretty much means you can't do anything to it. Since in this case you *are* trying to do something to it, it's just not going to work.

 

[andyfive]

Many Thanks for your reply xenou. I need the formatting to be able to pass the data from one Access table to another. The reason I wanted to open Excel from within Access was because it was inconvenient to have to split my Access macros in two, run the first set, close Access, open Excel to do the formatting, close Excel, Open Access again and then continue with the second set of Macros. It would have been much more convenient to run it all as on step within Access.

The solution has been to actually do the formatting from within an Access query and create a temporary table to work from (i.e. eliminate Excel from the process completely). Took a little time to convert the formatting commands from Excel to Access but all is working well now.

Anyway, thanks for verifying that the Excel root was not feasible, enabling me to successfully focus on an alternative solution.