3

I try to lock a small dummy text file on OneDrive in memory, by opening it Read/Write, so that I have exclusive access over some shared Excel tables in another open shared xlsm. After the necessary modifications, I will close the file to allow other users to lock it and modify the shared xlsm if necessary.

The Open and FSO.OpenTextFile commands work as expected on local files. I cannot get either to work, though, with a "https://d.docs.live.net/....." filepath. They throw a runtime error.

Workbooks.Open can handle such pathnames (as I read and tested myself in another SO post) but I do not wish to open the file in Excel, merely keep it locked in memory on an OS level until I finish my modifications.

Public Sub GetExclusiveAccess()

Dim fso As FileSystemObject
Dim txtStream As TextStream
Dim filepath As String
Dim PathDelimiter As String

PathDelimiter = IIf(InStr(ThisWorkbook.Path, "//") > 0, "/", "\")
filepath = ThisWorkbook.Path & PathDelimiter & "filelock.txt"

' First method

Set fso = New FileSystemObject
'On Error Resume Next
Set txtStream = fso.OpenTextFile(filepath, ForWriting, False)
'On Error GoTo 0
If txtStream Is Nothing Then GoTo Error
'Success...
txtStream.Close

' Second method

Open filepath For Output As #1
'Success...
Close #1

Error:

End Sub
gthalassinos
  • 31
  • 7
  • As far as I know you can't lock textfiles the way you describe. Notepad will not notice that someone else has the file open. Is it the actual text file that you need to change contents in or is it another file? – Andreas Nov 11 '19 at 14:25
  • No, users have each their own code.xlsm open and a shared data.xlsm. I need to modify the data.xlsm. I tried my code with a local file ("C:\Test\filelock.txt") and the code did not throw the runtime error. Being in debug mode I switched to a Windows Explorer window and tried to delete the local file, which was denied as being open from Excel. This is what I would expect to be enough. I didn't actually try to delete it from another computer to test if I would get the same denial. – gthalassinos Nov 11 '19 at 14:33
  • Try the UNC version of the path: https://stackoverflow.com/questions/58752868/open-most-recent-workbook-in-a-folder-on-sharepoint-document-library/58754848#comment103825656_58754848 – Tim Williams Nov 11 '19 at 16:11

1 Answers1

0

I see you want to build the exact same set up as me.
I suggest read and write to the file lock text file instead.
Files can get stuck and not close when they should, but changing the contents is by my experience safer.

I have a text file with the word "open" or "closed" depending on the value it will open the shared database file. 

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(TXT_File_Path, 1)
Contents = objFile.ReadLine
DoEvents
objFile.Close

if Contents = "Open" then
    ' output error
else
    ' open file

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.CreateTextFile(TXT_File_Path)

    objFile.WriteLine "Open" ' write open in the text file
    objFile.Close

    Set FSO = Nothing
    Set oFile = Nothing


    ' do your stuff


    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.CreateTextFile(TXT_File_Path)

    objFile.WriteLine "Closed" ' write Closed in the text file
    objFile.Close

    Set FSO = Nothing
    Set oFile = Nothing
end if

there is also a function that I believe I found on Microsoft help pages called IsWorkBookOpen.

Function IsWorkBookOpen(FileName As String)
    Dim ff As Long, ErrNo As Long

    On Error Resume Next
    ff = FreeFile()
    Open FileName For Input Lock Read As #ff
    Close ff
    ErrNo = Err
    On Error GoTo 0

    Select Case ErrNo
    Case 0:    IsWorkBookOpen = False
    Case 70:   IsWorkBookOpen = True
    Case Else: Error ErrNo
    End Select
End Function

This will return true/false if a workbook (Excel) is open or closed.
But I mainly rely on the textfile since that is faster and can hold extra information (which mine does, last opened).

I also advice you to change to xlsb files since they are faster than xlsm. Having a database file like this that needs to be opened and closed lots of times will take a long time when the file reaches only about 2 MB via network.

Andreas
  • 23,610
  • 6
  • 30
  • 62
  • Thanks for the detailed answer. The **IsWorkBookOpen** is a version I had not come across until now. Thanks for that too. The "Open"/"Close" contents of the file is nice too! One or two points though.... My data.xlsm file is always open, I do not Open/Close it. My app reads from it all the time. It is sometimes that a user has to edit some field or **append records to a table**. That append action is the deeper need to block others from modifying it at the same time. The new records need to get a sequential RecordID, which will be calculated at that stage. – gthalassinos Nov 11 '19 at 15:28
  • 1
    The other thing is my main question, how to manage to open the file in memory when its path is a OneDrive URL. You use Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFile = objFSO.OpenTextFile(TXT_File_Path, 1) which in my case throws an error. – gthalassinos Nov 11 '19 at 15:28
  • You can't use a network drive on the workplace? SharePoint, onedrive and such is usually hard to use in Excel. How can you have the file open all the time if others want to write to it? Excel only allows one to write to a file at the time. Not sure I understand the full setup. – Andreas Nov 11 '19 at 15:56
  • This app is for 2 users in different sites and I am exploring the possibility to leverage OneDrive's shared access/update feature with Excel AutoSave. If this proves to be not feasible I shall have to resort to some sort of open/close readonly/read-write setup, which as you already mentioned is not very efficient. – gthalassinos Nov 11 '19 at 16:26
  • But per my tries, that was the only option. I also tried to share the workbook but each time one made changes the other user could not merge his changes with previous changes. – Andreas Nov 11 '19 at 16:34
  • 1
    I put my theory to the test and it works as I thought. When I open my data.xlsm from OneDrive from my desktop and my laptop (it's not a LAN connection since my laptop is on WiFi on a discrete subnet and traffic goes over the Internet) I can see 2 cursors moving on the same worksheet on both machines and any changes made to one are replicated to the other (AutoSave = On). MS syncs changes on a cell basis so it's quite efficient.I think it will be even more so if I split the data.xlsm to smaller data files with less sheets. – gthalassinos Nov 11 '19 at 17:39