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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,720
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,720
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,099,256
Messages
5,467,607
Members
406,544
Latest member
Aditya_Shanmugham

This Week's Hot Topics

Top