Multiple users for excel workbook

nachiketdp

Board Regular
Joined
Jan 31, 2007
Messages
53
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
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,734
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
 

nachiketdp

Board Regular
Joined
Jan 31, 2007
Messages
53
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?
 

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,734
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
 

nachiketdp

Board Regular
Joined
Jan 31, 2007
Messages
53
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! :)
 

Watch MrExcel Video

Forum statistics

Threads
1,102,593
Messages
5,487,755
Members
407,610
Latest member
bellakim00

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top