
Power Query

January 30, 2020

Afternoon All,
I have found a VBA code that would prevent 2 or more using a excel file
When opening a file a InUse_Yes.txt that would drop in together with the excel file.
I looked at this VBA file which may have some merit to its name but these is a run time error 52 Bad File Name or Number.
Option Explicit
Const FileControlFolder = "FileControlFolder = C:\Database\FilesInUse"""
Dim FileControlSubFolder As String
Private Sub Workbook_Open()
If ThisWorkbook.ReadOnly = False Then
FileControlSubFolder = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 5)
If Dir(FileControlFolder & FileControlSubFolder & "\InUse_YES.txt") <> "" Then
MsgBox "It seems that the " & FileControlSubFolder & _
" file is being used by another user. Please come back later.", _
vbCritical, "More than one user accessing the file."
ThisWorkbook.Close SaveChanges:=False
Else
Name FileControlFolder & FileControlSubFolder & _
"\InUse_NO.txt" As FileControlFolder & FileControlSubFolder & "\InUse_YES.txt"
TextFile_Create (FileControlFolder & FileControlSubFolder & "\" & Environ("UserName") & ".txt")
End If
FileControlSubFolder = vbNullString
End If
End Sub
Thanks
Steve


Trusted Members
Moderators

November 1, 2018


Power Query

January 30, 2020

Yes thanks Velouria,
I found that out just as i finished work and changed that very line.
Now I get the error in filename not defined????
Name FileControlFolder & FileControlSubFolder & _
"\InUse_NO.txt" As FileControlFolder & FileControlSubFolder & "\InUse_YES.txt"
TextFile_Create (FileControlFolder & FileControlSubFolder & "\" & Environ("UserName") & ".txt")
Still figuring it out!
Thanks
Steve
1 Guest(s)
