Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Location
    Sweden
    Posts
    5

    Question Unanswered: Howto keep two worksheets identical?

    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

  2. #2
    Join Date
    Sep 2003
    Location
    Cincinnati, Oh USA
    Posts
    203

    Re: Howto keep two worksheets identical?

    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

  3. #3
    Join Date
    Aug 2003
    Posts
    106
    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.

  4. #4
    Join Date
    Oct 2003
    Location
    Sweden
    Posts
    5
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •