Multiple users for excel workbook
Results 1 to 5 of 5

Thread: Multiple users for excel workbook
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jan 2007
    Posts
    53
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Multiple users for excel workbook

    Dear All,

    I am using Excel 2010 in my office. I have developed an excel file for maintaining data related to sale of inventory and receipt of moneys from debtors. I want multiple users in my office to work upon it concurrently. However, this is not happening very smoothly. I tried sharing the file on dropbox but it is generating conflicted files. I have got many macros, userforms and pivot tables embedded in my file so I can't 'share' the workbook.

    Can someone suggest me a solution whereby my excel file becomes like a software accessible to all simultaneously? Your responses will be truly appreciated as this issue has been gnawing me since several days!

    Thanks in advance,
    Nachiket Pendharkar

  2. #2
    Board Regular Jeffrey Mahoney's Avatar
    Join Date
    May 2015
    Location
    Northwest Washington
    Posts
    1,523
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multiple users for excel workbook

    I have done that in a large way. Of course, my main purpose was take new inputs and save them into an external excel file. The main template guided users to input data, then when they were finished, it saved all data into one database file. The Template was set as read only so they couldn't make permanent changes. Everybody that needed to use it had access to the server folder. Every time the template was opened, it imported the main database to give them fresh data. I also allowed them to import the main DB during their session.

    In your case, with out of network users, I don't see that being practical.

    Other methods are to have satellite workbooks. Whenever a user sends you their workbook, the master imports their new entries and incorporates them into the central database. You would have to supply new versions after all the satellite workbooks were updated for the day. This version is actually easy to maintain. The only drawback is that users cannot change records that others are also maintaining. Your team would have to have designated records that others didn't touch.

    Lots of work.

    Sharing workbooks DOES NOT WORK. Excel got that part wrong.

    Jeff
    Have you been frustrated searching the Mr Excel site using the internal search feature. Use Google instead:
    In your Search Bar, type: site:www.mrexcel.com/forum/ Search Words
    -------------------------------------------------------------------
    If I were in charge, I'd ask for opinions, and actually consider them!

  3. #3
    Board Regular
    Join Date
    Jan 2007
    Posts
    53
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multiple users for excel workbook

    Thank you very much, Mr Mahoney! Yes, I was also thinking along the same lines as I did lot of googling and have come to the conclusion that I cannot have a single workbook holding the data as well as the interface for the users. So now I am thinking of maintaining several database workbooks, each containing their own data and every user will have an interface file. The database files will be shared workbooks. The interface files will keep communicating with the database files and will input/retrieve data from them. I will use a dropbox location as the common location to host the database files while interface files may be located anywhere on the local drive.

    What's your opinion on this?

  4. #4
    Board Regular Jeffrey Mahoney's Avatar
    Join Date
    May 2015
    Location
    Northwest Washington
    Posts
    1,523
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multiple users for excel workbook

    Using VBA to retrieve data from satellite workbooks is better than formula linking.

    You can open a workbook as READ ONLY and get the contents faster than opening a workbook as a shared workbook and reading live data. This works if the data is only going one direction > into the Master. It works best if you overwrite your local files from dropbox, keeping the same name as before. Build a table with a list of satellite workbook names.
    Code:
    Set TSWB = Workbooks.Open(PathFile, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
    If you have bi-directional data, meaning, the satellite workbooks need to have a copy of the full database, then you will have to schedule a period of time each day where the satellite workbooks are not being updated. You run your updates and send the updated files back to dropbox. That means you'll have to update all satellite workbooks after you pull all the data from them.

    With that, let me know how I can help. I have to work full time also. Post some code and we'll get you started.

    Jeff
    Have you been frustrated searching the Mr Excel site using the internal search feature. Use Google instead:
    In your Search Bar, type: site:www.mrexcel.com/forum/ Search Words
    -------------------------------------------------------------------
    If I were in charge, I'd ask for opinions, and actually consider them!

  5. #5
    Board Regular
    Join Date
    Jan 2007
    Posts
    53
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Multiple users for excel workbook

    Sorry for the late reply, I was tied up in some year ending processes (In India, our financial year ends on 31st March). I appreciate all your inputs, I am now developing the work as discussed earlier on this thread. I will stay in touch with you regarding the progress.

    Thank You!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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