PDA

View Full Version : Howto keep two worksheets identical?

Krulle
10-04-03, 12:13
It started of with me programming a VB-script into an Excel-file in order to run through one of the worksheets, select some of the rows and then print them. Simple as it was, I thought it was enough to use Excel. No databse connections where needed. But the project grew...

The problem I have now is that I must use the file on several computers, and since there are limitations in what you can do in shared workbooks my macros don't work correctly. The user does also have to be able to manipulate the data in the file. But when he does so, the two files aren't identical anymore.

How can I link the files alowing the user manipulate the data in anyone of the three files while all of them get updated with the changes?

I've been trying with having a linked table between Access and one of the Excel-files. The problem that appears for me then is, that I'm only able to update the other files through the "masterfile" that is linked to Access but not vice versa.

I would be REALLY glad if someone could help me out!


Thanks in advance!
/Kristoffer
Stockholm - Sweden
RickKnight
10-04-03, 18:42
What are the limitations in Excel (shared) that is creating the problem(s) you are encountering? Excel can be shared with up to 256 users at a time. The major limitations (see Excel Help > shared > limitations) are those which generally do not limit what the user can do to manipulate or add data. The macros if defined as avaliable in "This workbook" should work across all systems. I have found that background coding in VBA is stronger than vbscript (vbscript will require all user's machines be able to run script). This (vbscript) possibly could the problem.


Originally posted by Krulle
It started of with me programming a VB-script into an Excel-file in order to run through one of the worksheets, select some of the rows and then print them. Simple as it was, I thought it was enough to use Excel. No databse connections where needed. But the project grew...

The problem I have now is that I must use the file on several computers, and since there are limitations in what you can do in shared workbooks my macros don't work correctly. The user does also have to be able to manipulate the data in the file. But when he does so, the two files aren't identical anymore.

How can I link the files alowing the user manipulate the data in anyone of the three files while all of them get updated with the changes?

I've been trying with having a linked table between Access and one of the Excel-files. The problem that appears for me then is, that I'm only able to update the other files through the "masterfile" that is linked to Access but not vice versa.

I would be REALLY glad if someone could help me out!


Thanks in advance!
/Kristoffer
Stockholm - Sweden
sugarflux
10-05-03, 09:07
Another way (but much more complicated) would be to have the one master spreadsheet sitting on the server. Create a copy of the spreadsheet for each machine and write VB to update the master document from the local copy when a button on each of the copies is pressed.

It's slightly messy but depending on how long you want to spend on it, you can make it pretty secure using VB.
Krulle
10-05-03, 10:33
The alternative I've been thinking about, if there's no way to automate it, was using a DAO/ADO connection to an access-database. What solution would you recomend? Is it unnecesary to take the way over access?