0

Scenario

I have an excel file that contains data. There are multiple users accessing the file at the same time.

Problem

There will be problem if multiple users tried to input data to that excel file at the same time due to only one user is allowed to open the file at one time

Question

Is there any way whereby I can update the excel file (Eg: add a value to a cell, delete a value from a cell, find a particular cell etc) without opening it so that multiple users can update it at the same time using excel VBA?

Anu
  • 1,123
  • 2
  • 13
  • 42
  • 1
    According to this article, you can still use the old "Share Workbook" functionality. I was able to enable it for a 2016 workbook. https://www.presentationpoint.com/blog/multiple-users-excel-2016-datasheet/ – DeanOC Jul 09 '18 at 03:38
  • File is now able to update by different users at the same time after enabled the legacy shared option. Let me try some VBA codes to update the file and see if it works also. Thank you @DeanOC – Anu Jul 09 '18 at 05:07
  • Not sure who downvote the question without mentioning reason for the downvote. – Anu Jul 09 '18 at 05:07
  • @DeanOC The sharing works fine. But now that problem is the moment when I turned on sharing, VBA seems like disabled. I cannot open Macros anymore. So I still need to find a way to update the cells without opening it. – Anu Jul 10 '18 at 02:17
  • I thought you were only using VBA as a workaround to emulate multiple users, but it sounds like you want to have users editing the document at the same time as having code modify the file. Sorry, can't help you with that one. – DeanOC Jul 10 '18 at 04:49

1 Answers1

1

I went to the direction of using shared files. But later found out to be excel shared files are very buggy. If use shared file, excel/macro can be very slow, intermittent crashes and sometime the whole file may get corrupted and could not be opened or repaired afterwards. Also depends on how many users use the file, the file size can grow quite big. So it is best not to use shared workbook. Totally not worth trying. Instead if need multiple users to update data simultaneously, it is better to use some database such as MSAccess, MSSql (Update MSSQL from Excel) etc with excel. For my situation since number of users are less, I didn't use any database, instead put a prompt for the user to wait until the other user close that file. Please see the codes below to check if a file is opened and if so, to prompt user. I got this code from stack overflow itself and I modified to suit my needs.

Call the module TestFileOpened as below. 

Sub fileCheck()
  Call TestFileOpened(plannerFilePathTemp)
    If fileInUse = True Then
        Application.ScreenUpdating = True
        Exit Sub
    End If
End Sub

Here plannerFilePathTemp is the temporary file location of your original file. Whenever an excel file opened, a temp file will be created. For example, your original file location is as below

plannerFilePath = "C:\TEMP\XXX\xxx.xlsx"

Thus your temporary file location will be

plannerFilePathTemp = "C:\TEMP\XXX\~$xxx.xlsx"

or in other words, temporary file name will be ~$xxx.xlsx

The following codes will be called upon Call TestFileOpened(plannerFilePathTemp)

Public fileInUse As Boolean
Sub TestFileOpened(fileOpenedOrNot As String)
Dim Folder As String
Dim FName As String

Set objFSO = CreateObject("Scripting.FileSystemObject")

If objFSO.FileExists(fileOpenedOrNot) Then
    fileInUse = True
    MsgBox "Database is opened and using by " & GetFileOwner(fileOpenedOrNot) & ". Please wait a few second and click again", vbInformation, "Database in Use"
Else
    fileInUse = False
End If

End Sub

Function GetFileOwner(strFileName)
    Set objWMIService = GetObject("winmgmts:")
    Set objFileSecuritySettings = _
    objWMIService.Get("Win32_LogicalFileSecuritySetting='" & strFileName & "'")
    intRetVal = objFileSecuritySettings.GetSecurityDescriptor(objSD)

    If intRetVal = 0 Then
       GetFileOwner = objSD.Owner.Name
    Else
       GetFileOwner = "Unknown"
    End If
End Function

I encountered Out of memory issues also when used shared files. So during the process, I figured out the following methods to minimize memory consumption

Some tips to clear memory

Anu
  • 1,123
  • 2
  • 13
  • 42
  • 1
    When I've been _forced_ to try multi-user entry in a spreadsheet I generally go with each user having their own daily workbook and a manager has a master file that copies all the information over when they press a button. – Darren Bartrup-Cook Sep 25 '18 at 07:54
  • @DarrenBartrup-Cook That is a good approach too. But for my situation, it can't be applied due to there is something that is forcing everyone to update in one single file rather than separate files. – Anu Sep 25 '18 at 08:30