Quote:
|
Originally Posted by BobBridges
You said other people, up to 12, may be using that workbook. But are they updating the workbook, or only reading it?
|
Quote:
|
Originally Posted by Nicholaspoe
Correct. They are doing updates but none that need to be saved. Using the data wizard I connected workbook A to workbook B already and it works but i can only open workbooks A in read only unless Workbook B is closed and vice versa.
My team will be making their own edits to the workbook throughout the day but if this works would press a refresh button to receive the new data from workbook A.
Can I import workbook A into access and link it to another local table that could be imported into workbook B?
|
What I'm thinking is that if you can get these users to open Workbook B read-only (since they're not going to be updating it), then I should think Excel
will accept your program making changes to it while they have it open. And in that case you don't need Access at all; the process you already have working will do the job for you.
To test this hypothesis, have one of the users open Workbook B with the Read-Only option, and while you know he has it open with read-only, and no one else has it open at all, try updating it with changes. I think it'll work.
If a user just double-clicks on a workbook icon, Excel won't open it read-only. There are two ways I know of to do it:
1) In Excel, use the dialogue in
File,
Open. Point to the workbook you want to open. Instead of hitting the
Open button, click on the down arrow next to it and select "Open Read-Only". Now the file is open in read-only mode, and it'll say "[Read-Only]" in the title bar; if the user absentmindedly tries to save it, Excel won't allow it, but will insist that he use Save
As to a different name if he wants the changes to be saved anywhere.
2) You can remind your users to open as read-only (and incidentally allow them to open by double-clicking on the icon in Windows) by setting the "Read-only recommended" switch for the workbook. To do this, use Save
As. Before clicking on the Save button, select the Tools drop-down menu beside it, then General options. Set the "Read-only recommended" checkbox. After clicking OK,
then you can save. After that, if anyone double-clicks on the icon, Excel will pop up a message saying that read-only is recommended for this workbook and offering them a choice.
Now, both methods are voluntary; your users could, if they chose, open the workbook without the read-only setting and make changes to it. Maybe that's the way you want it. But if you decide you want to make it harder, there are fancier things you could do. You could, for example, make it read-only in Windows; after that, no one can update it without changing that setting. Your program would have to temporarily turn off the read-only switch, update the data and then turn read-only back on. Someone
might happen to open the file for update during the few seconds your program was doing that, but it's unlikely.
Or you could get your Windows security people involved, setting up the access rules so that only a certain account (the one you use to save changes) can update it and everyone else is read-only.
But if you want others to be able to update Workbook B on occasion—just not to do it normally, or forgetfully—then the "Read-only recommended" switch sounds like the right bet to me.