[work around] $oActiveWorkbook.UserStatus not working

[Simpel]

Hi.

I try to figure out who is using a excel workbook which I can only open "read only". I use this code:

#include <Array.au3>
#include <Excel.au3>
Local $sFile = ; excel file with path on a network drive

Local $oExcel = _Excel_Open(True, True)
Local $oTabelle = _Excel_BookOpen($oExcel, $sFile)
Local $aUsers
If IsObj($oTabelle) Then
    $aUsers = $oTabelle.UserStatus
    _ArrayDisplay($aUsers)
EndIf

If I am the one allowed to write to the excel file (I'm the first one who opened it) then I will get an array with myself:

If my collegue opened the excel file first and I run the code I get the following error message:

"H:\_Conrad lokal\Downloads\AutoIt3\_COX\Tests\test.au3" (9) : ==> The requested action with this object has failed.:
$aUsers = $oTabelle.UserStatus
$aUsers = $oTabelle^ ERROR

The excel file is on a network drive. Is that's the problem?

Regards, Conrad

Edited May 10, 2018 by Simpel
[work around]

[water]

You need to add some kind of error checking to your script:

#include <Array.au3>
#include <Excel.au3>
Local $aUsers, $sFile = ; excel file with path on a network drive
Local $oExcel = _Excel_Open(True, True)
If @error Then Exit MsgBox(0, "Error", "Error " & @error & " opening Excel!")
Local $oTabelle = _Excel_BookOpen($oExcel, $sFile)
If @error Then Exit MsgBox(0, "Error", "Error " & @error & " opening Workbook!")
If @extended = 1 Then ; Workbook could not be opened as read/write
    $aUsers = $oTabelle.UserStatus
    _ArrayDisplay($aUsers)
EndIf

 

[Simpel]

Hi @water,

currently I can't try it out (in the evening I can) but I think I will run into this error too. As I wrote I run into this error if the excel file is opened read only by me. Than @extended will be 1 and then $oTabelle.UserStatus will fail with object $oTabelle. Or am I wrong?

Conrad

[water]

Seems property UserStatus does not work the way we expect it to work.
Details and a solution can be found here: https://stackoverflow.com/questions/15555522/how-to-tell-if-an-excel-2007-spreadsheet-is-open-and-who-has-it-open-using-vbscr

[Simpel]

Thanks @water for this hint. I couldn't find anything about it. This is my whole solution now:

expandcollapse popup

#include <File.au3>
Local $sFile = ; fullpath to an excel file
Local $sOpened = _ExcelFileOpened($sFile)
If @error Then ; nobody has opened this excel file
    If @error = 1 Then
        ConsoleWrite("You can open the excel file and write to it." & @CRLF)
    ElseIf @error = 2 Then
        ConsoleWrite("File does not exist." & @CRLF)
    ElseIf @error = 3 Then
        ConsoleWrite("File ist not an excel file." & @CRLF)
    EndIf
ElseIf @extended = 1 Then
    ConsoleWrite("This excel file is already opened by you with write privilege" & @CRLF)
Else
    ConsoleWrite("This excel file is opened by " & $sOpened & @CRLF & "You can't write to the file therefor it will not be opened." & @CRLF)
EndIf
Exit


; #FUNCTION# ====================================================================================================================
; Name ..........: _ExcelFileOpened
; Description ...: Returns who has the excel file opened with write privilege
; Syntax ........: _ExcelFileOpened($sFile)
; Parameters ....: $sFile - must be an excel file
; Return values .: Success - name of who has opened the excel file with write privilege, sets @extended to:
;                  |1 - if I am the one
;                  Failure - 0, sets @error to:
;                  |1 - excel file is not opened
;                  |2 - file does not exist
;                  |3 - file is not an excel file
; Author ........: Simpel
; Modified ......:
; Remarks .......:
; Related .......:
; Link ..........:
; Example .......: No
; ===============================================================================================================================
Func _ExcelFileOpened($sFile)
    ; check if file exists
    If FileExists($sFile) = 0 Then Return SetError(2, 0, 0)
    ; check if file is an excel file
    Local $iDelimiter = StringInStr($sFile, ".", 0, -1) ; splits suffix
    Local $sExtension = StringTrimLeft($sFile, $iDelimiter) ; only file extension
    If StringLeft($sExtension, 3) <> "xls" Then Return SetError(3, 0, 0)
    ; if excel has created a temporary excel file with a ~$ prefix then it is already opened
    $iDelimiter = StringInStr($sFile, "\", 0, -1) ; splits file from path
    Local $sTempFile = StringLeft($sFile, $iDelimiter) & "~$" & StringTrimLeft($sFile, $iDelimiter) ; adds prefix ~$ at excel filename
    Local $iTempFileExist = FileExists($sTempFile) ; if this file exists then the excel file is opened
    If $iTempFileExist = 0 Then Return SetError(1, 0, 0) ; is not opened
    Local $sOwnerTempFile = _Owner($sTempFile) ; owner of the excel temp file is the one with write privilege
    ; find out who I am
    Local $sTestFile = _TempFile() ; needs file.au3
    FileWrite($sTestFile, "") ; create a test file (I am definitely the owner)
    If @error Then
        Return $sOwnerTempFile ; returns only the one with write privilege to the excel file
    EndIf
    Local $sMe = _Owner($sTestFile) ; this is me
    FileDelete($sTestFile)
    ; look if I am the one created the temp excel file
    If $sOwnerTempFile = $sMe Then
        Return SetError(0, 1, $sMe) ; returns me opened excel file with write privilege and sets @extended to 1
    Else
        Return $sOwnerTempFile ; returns the one with write privilege to the excel file
    EndIf
EndFunc

Func _Owner($sFile) ; the one who saved it last - code by siao?
    Local $secUtil = ObjCreate("ADsSecurityUtility")
    Local $secDesc = $secUtil.GetSecurityDescriptor($sFile, 1, 1)
    Local $sOwner = $secDesc.Owner
    $secUtil = Null
    Return $sOwner
EndFunc

Regards, Conrad

[water]

Looks great!
Will test as soon as I find some spare time