Modifying records in Excel from Access

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

vcsphx - 08 May 2007 22:45 GMT

All,
I have got 14 recordsets that I export to a single Excel workbook, with 14
spreadsheets, every week. The problem I am having is, there are some
occurences where people change the recordset in the middle of the week and I
need to update that change for the affected records in Excel. The front end
user of Excel keeps the file always open that makes my updating process
difficult. Is there a way I can update the excel spreadsheets without having
the user closes the application? Or is there a way I can notify the user that
the file will be closed in some time for update and I can control from Access
to close the file and do the update and reopen it? I would appreciate any
help in this regard.

Thanks!

Reply to this Message

Scott McDaniel - 09 May 2007 13:56 GMT

>All,
>I have got 14 recordsets that I export to a single Excel workbook, with 14
[quoted text clipped - 7 lines]
>to close the file and do the update and reopen it? I would appreciate any
>help in this regard.

Unless you're automating Excel through Access (and it doesn't sound like you are) then you can't really control that
session from Access (at least to my knowledge). However, you say you're Exporting data to Excel ... so what would stop
you from simply doing another Export when the Access data changes? Or are you saying that you're trying to Export to an
in-use Excel worksheet?

Would it be easier to simply Link the data in Access from Excel? You can set up the link so that it's readonly (if
necessary) and therefore you wouldn't need to do anything, the Excel users could simply refresh their links to get the
latest information.

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

Reply to this Message