• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel UF on Shared Drive

S

Sameer.k21

Member
Hi All,

I have a userform tool for about 10users. Instead of distributing the files - If I upload or save this Excel based userform tool on a Shared Drive for all 10users to use/update/edit/save data at same time, will it have everything in one file?

Your response or ideas will be appreciated. Thank you.
 
Hi Sameer ,

The point is that Excel has not been developed for use in a multi-user environment ; you can still have your Userform tool on a shared drive but you have to either warn users or restrict multiple users from accessing the tool simultaneously.

As long as only one user has the file open at any point in time , and the next user accesses the file after it has been closed by the previous user , there should not be any problem.

Narayan
 
@ Sameer.k21


For what Narayan has pointed, you can create something like as below to allow/disallow users to access.

1. Create login by userid.
2. Up on login. inject a script like as below

Code: 
Sub on_press_login()
myfolder = "c:\myfolder": myV = myfolder & "\user_logged.txt"

fileNumber = FreeFile()

If Len(Dir(myV)) > 0 Then
    MsgBox "Seems other user have already logged in", vbCritical
  '  close/exit from login screen
End If

If Len(Dir(myfolder, vbDirectory)) = 0 Then MkDir myfolder
    Open myV For Output As #fileNumber: Close #fileNumber

End Sub
3. After close/logoff. Something like as below
Code: 
Sub on_press_logoff_close()
myfolder = "c:\myfolder": myV = myfolder & "\user_logged.txt"
    Kill myV
      '  close/exit from login screen

End Sub

this will cover up 90 % hassle until uf/wb close expectedly.
 
@Sameer.k21
It can work!
I've used shared Excel-files with more that 10 users and of course in same time for many years.
Even if users use same cell same time and settings are Okay!
Users could get notes, which value is valid.
Of course, there are challenges as well as everywhere!
 
It depends on limitations exposed in my previous link,
the reason why I wrote « maybe only one user » …

There are some add-ins (not free !) to work without limitation
but often less smooth than Access or any true database software.
​
 
Other alternative to Access is to use SharePoint (Foundation 2010 is probably best option as it is free). It can be set up with free version of MS SQL (10GB database size limit, which should be more than plenty for most application).

Of course, this requires server side implementation.
 
Not being a very trusting soul, I have been concerned about using the Excel share workbook functionality but I do have the option and was planning to use the option of putting the file on our company's Sharepointe site. Currently I prevent multi-user simulatneous use by requiring the file be checked out by one user at a time (again because I'm just not very trusting). Any tips or warnings on lifting that and allowing file sharing functionality in Sharepointe? Does the Excel workbook sharing have to be turned on for Sharepointe sharing to work?
Is this an extension of the current thread or should I be starting a new thread for this? My apologies if I was supposed to start a new thread.
 
Sharepoint has - versioning option - this created different versions as and when saved.
Shared Drive option was to use one single file when 10users can work in same time and data gets updated same time which should be visible to all - plus there is an option to save the Shared Excel every 5mins.

I will try shared drive and update the outcome here - lets see how it goes.
 
A point - in case you have Sharepointe 2010: the shared use by multiple concurrent users works when users all choose to edit in the web browser but not if you open the file outside of the web browser. At least that was what I encountered with Sharepointe 2010 and MS Office 2010.
 
BelindaJoy said:
A point - in case you have Sharepointe 2010: the shared use by multiple concurrent users works when users all choose to edit in the web browser but not if you open the file outside of the web browser. At least that was what I encountered with Sharepointe 2010 and MS Office 2010.
Click to expand...
Awesome...So I can have a excel based UF on Sharepoint and use that on web with about 10+ users accessing at the same time and it is going to work fine??
 
I've tried out the multiple user access to an ordinary excel file with no vba or userforms on Sharepointe 2010 using the Edit browser option with success. I've not tested this on a file with any vba macros or a user form in particular so I can't offer assurance there will be no problems, maybe another user has had experience with that.
 
I tried with Sharepoint - Macro feature does not work here. It only displays a basic excel sheet and when you download the file - that will be with macro enabled feature.
 
Thanks for that update, that is bad news for me too because I similarly need to se up to enable multi-user access to macro enabled file from Sharepointe. Well IT is willing to talk to me now about a possibility setting up a Sharepointe web-part to do what I need rather than the excel file... we'll see what timeline they'll commit to and how much capability is possible without the "customizing" they object to.
 
You must log in or register to reply here.
Back
Top