File in Use - file is locked for editing - Need VBA to handle this issue

[jfarc]

I have an automated unmanned workbook 'Alpha' periodically running a macro to open up workbook 'Beta' to process data and then save workbook 'Beta'. But, workbook 'Beta' can also be opened by another user to perform updates/save on their own. If 'Beta' is already open by a user and 'Alpha' macro doesn't open it and returns the 'File in Use - file is locked for editing' error box.

My goal would be for the 'Alpha' macro to prevent the error box from opening and simply ascertain that 'Beta' is 'locked' and handle it with alternate code.

Sort of like this:

Workbooks.Open Filename:=  "C:\Beta"
If "Beta" is locked Then
    do this.....
Else
    do that....
End If

 

[ChrisM]

This handy function was posted on stackoverflow:

Function IsWorkBookOpen(FileName As String) as Boolean
    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

So to use it in your case, run the function BEFORE you try to open your file:

If IsWorkBookOpen("C:\Beta") = False Then
         Workbooks.Open Filename:=  "C:\Beta"
Else
   'code if file open already

End If

 

[dmt32]

Hi,
see if this function does what you want:

Function FileInUse(sFileName As String) As Boolean
    On Error Resume Next
    Open sFileName For Binary Access Read Lock Read As #1
    Close #1
    FileInUse = IIf(Err.Number > 0, True, False)
    On Error GoTo 0
End Function

to test

Private Sub TestFileInUse()
    Dim sPath As String
    Dim sFileName As String

        'change as required
    sPath = "S:\MyFolder\MyFolder2\"
    sFileName = "FileName.xlsx"
    
    If FileInUse(sPath & sFileName) Then
        'read / write file in use
        'do something
    Else
        'all ok
        'do something else
    End If
End Sub

Dave

 

[jfarc]

Great! I will try both responses. Already into something else for the rest of day, so I'll look at them tmrw. Thanks!

 

[MPW]

What is the method to find out who has the workbook open? I need to update a content log and over a 100 people can access it. It would be handy to be able to know who to contact if the automatic update fails.

 

[keepintouch]

I have a similar issue while running the VB script which I'm using in SAP whenever i run this script the excel sheet gets locked and the error message i get is "unable to write read-only property" and i see the property of excel sheet it says "Read Only". Please help where I'm going wrong. Thanks in advance

If Not IsObject(application) Then
Set SapGuiAuto = GetObject("SAPGUI")
Set application = SapGuiAuto.GetScriptingEngine
End If
If Not IsObject(connection) Then
Set connection = application.Children(0)
End If
If Not IsObject(session) Then
Set session = connection.Children(0)
End If
If IsObject(WScript) Then
WScript.ConnectObject session, "on"
WScript.ConnectObject application, "on"
End If
session.findById("wnd[0]").maximize
Dim objExcel, objWorkbook, objSheet, i
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Users\kals0\OneDrive\Documents\Business_Area5.xlsx")
Set objSheet = objWorkbook.Sheets("Sheet1")
For i = 2 to objSheet.UsedRange.Rows.Count


Business_Area = Trim(CStr(objSheet.Cells(i, 1).Value)) 'Column1
Name = Trim(CStr(objSheet.Cells(i, 2).Value)) 'Column2


session.findById("wnd[0]/tbar[0]/okcd").text = "ox03"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/tbar[1]/btn[5]").press
session.findById("wnd[0]/usr/tblSAPL0ORGCORETCTRL_V_TGSB/txtV_TGSB-GSBER[0,0]").text = Business_Area
session.findById("wnd[0]/usr/tblSAPL0ORGCORETCTRL_V_TGSB/txtV_TGSB-GTEXT[0,0]").text = Name
session.findById("wnd[0]/usr/tblSAPL0ORGCORETCTRL_V_TGSB/txtV_TGSB-GTEXT[0,0]").setFocus
session.findById("wnd[0]/usr/tblSAPL0ORGCORETCTRL_V_TGSB/txtV_TGSB-GTEXT[0,0]").caretPosition = 12
session.findById("wnd[0]/tbar[0]/btn[11]").press
next
msgbox "Created Business Areas"

 

[keepintouch]

I have a similar issue while running the VB script which I'm using in SAP whenever i run this script the excel sheet gets locked and the error message i get is "unable to write read-only property" and i see the property of excel sheet it says "Read Only". Please help where I'm going wrong. Thanks in advance

Can anyone help with some suggestions. Thanks

 

[pbornemeier]

Way late response, but got to this thread during my search so I though I would attempt an answer.

If you execute this code in a different Excel workbook,

Workbooks.Open("C:\Users\kals0\OneDrive\Documents\Business_Area5.xlsx")

Is the Business_Area5.xlsx file opened as read only?

At the Excel level,
It may be saved with read-only recommended.
It may require a password to open in write mode.
It may be opened by someone else and not shared.

On a system level
The process running may not have write access to the folder containing the file.