+ Reply to Thread
Results 1 to 4 of 4

Updating by multi-user from one workbook

  1. #1
    Forum Contributor
    Join Date
    04-23-2007
    Location
    Texas
    Posts
    417

    Red face Updating by multi-user from one workbook

    What I have is one workbook with 21 sheets. Within each sheet I have a VB add-in that query’s an oracle database and pulls values into the sheet. This workbook cannot be locked because if locked, the queries will not pull in the values. What I’ve done is hide this workbook in a place where other users can’t find it.

    The users see 21 other separate workbooks and they use their workbook to update shift activities throughout a 12-hour shift. In their workbooks I will create formula’s to pull in values from the workbook doing the oracle queries and I can then hide the formulas.

    If more than one person opens his or her workbook at the same time I’m sure someone will get a read only error when the workbook try’s to update values from the main workbook. Is there a way around this? Keep in mind that the workbook with the 21 sheets takes about 1 min to update after it is opened. So if code was written in a way that would open the main workbook first someone might get the read only error if it's within the one min or so it takes the workbook to refresh data. Maybe there is a way to write code on the individual workbooks to stall them if the main workbook is open?... I'm not real good at this stuff! I’m not sure what to do about this but if I can make this work, it might save my job!!

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967
    You might be able to make use of

    Please Login or Register  to view this content.
    which will return a true if the workbook is open elsewhere as an early step in your code to prevent subsequent actions which might cause problems.
    Martin

  3. #3
    Forum Contributor
    Join Date
    04-23-2007
    Location
    Texas
    Posts
    417
    I don't know if the main sheet will update if it's open by others in read-only. I'll give it a try and see! Thats should be easy enough. I'll have to remember how to open a file in the background of the active workbook I think... would be cool if I could make the main sheet refresh data within the workbook every hour without opening it every hour.

  4. #4
    Registered User
    Join Date
    12-17-2007
    Posts
    9

    Hello

    Tortus,

    Please try my suggestion below as for the sharing of the Workbook. This will allow for the users to open, edit and save the workbook at the same time by different users.

    1. Save the workbook on a server network where everyone can access on their own computer.

    2. Make this workbook SHARED. If you are using 2007 go to Review and Click Share Workbook and Click "Allow changes by more than one user..."

    As for the other problem, you need a little bit of VBA coding to sort this out.


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1