Jump to content
Simpel

[work around] $oActiveWorkbook.UserStatus not working

Recommended Posts

Simpel
Posted (edited)

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:

UserStatus_Array.PNG.610210eb1edd63ae9956f14166a3fbcb.PNG

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 by Simpel
[work around]

SciTE4AutoIt = 3.7.3.0   AutoIt = 3.3.14.2   AutoItX64 = 0   OS = Win7Pro SP1   OSArch = X64   Language = 0407/german
H:\...\AutoIt3\SciTE     H:\...\AutoIt3      H:\...\AutoIt3\Include     (H:\ = Network Drive)

   88x31.png  Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind.

Share this post


Link to post
Share on other sites
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

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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


SciTE4AutoIt = 3.7.3.0   AutoIt = 3.3.14.2   AutoItX64 = 0   OS = Win7Pro SP1   OSArch = X64   Language = 0407/german
H:\...\AutoIt3\SciTE     H:\...\AutoIt3      H:\...\AutoIt3\Include     (H:\ = Network Drive)

   88x31.png  Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind.

Share this post


Link to post
Share on other sites
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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
Simpel

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

#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


SciTE4AutoIt = 3.7.3.0   AutoIt = 3.3.14.2   AutoItX64 = 0   OS = Win7Pro SP1   OSArch = X64   Language = 0407/german
H:\...\AutoIt3\SciTE     H:\...\AutoIt3      H:\...\AutoIt3\Include     (H:\ = Network Drive)

   88x31.png  Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind.

Share this post


Link to post
Share on other sites
water

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Similar Content

    • Skeletor
      By Skeletor
      Hi Guys,
      How would you use _Excel_FilterSet to filter the excel sheet from largest number to smallest number?
      _Excel_FilterSet($oWorkbook, "Sheet1", "A2:I2", 1, ">20", 1, "<40") ; OR _Excel_FilterSet($oWorkbook, "Sheet1", "A2:I2", 1, ">", 1, "<") Excel Sheet attached. Trying to filter on the last column "I2"
      Inventory.xlsx
    • Skeletor
      By Skeletor
      Hi All,
      Has anyone come across this before?
      Code below:
      $SheetList = _Excel_SheetList($oWorkbook) _FileWriteFromArray("C:\" & $SheetListOutput, $SheetList, 1) Result:
      ResultABC| ResultDEF| ResultGHI| ResultJKL| ResultMNO| It created these "|" vertical bars?
    • Skeletor
      By Skeletor
      Hi All,

      I would like to know how you would take a FileLineRead and insert it into an array which then inserts it into Excel?
      One thing to know is the files content is broken up, so I only use half of the content within $FileRead1.
      So its imperative that the $value1, $value2, etc variables be used. 
      Code below:
      $FileRead1 = FileReadLine("C:\temp\sample.txt",1) For $count = 1 To _FileCountLines($FileRead1) Step 1 $string = FileReadLine($FileRead1, $count) $input = StringSplit($string, ",", 1) $value1 = $input[1] $value2 = $input[2] $value3 = $input[3] $value4 = $input[4] _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value1, "A1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value2, "B1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value3, "C1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value4, "D1") Next  
    • AnonymousX
      By AnonymousX
      Hello,
      I'm trying to write a script that moves copies excel cells into an array. I'll than manipulate the values and send array into another program. 
      I don't want range to be specific to a workbook, or sheet, or set of cells.
      I want user to be able to highlight desired cells and to copy either normally ("Ctrl+C") or by a hotkey ("Alt+C"). 
      Could someone help me with this?
      Thank you,
      I've tried to write the framework: (edited)
      #include <MsgBoxConstants.au3> #include <Array.au3> #include <Excel.au3> HotKeySet("!v", "Pastedata") While True Sleep(1000) WEnd func Makearray() local $bArray ;User has cells already copied ;Convert clipboard into an array ;I don;t know how excel stores data to clipboard so don;t know how to bring it into array _Arraydisplay($bArray) MsgBox(0,0,$bArray) return $bArray endfunc func Pastedata() Local $aArray MsgBox(0,0,"wait",1) ;make array based on assumption user has already copied a range to clipboard $aArray = Makearray() ;paste code ;don;t worry about this I got the rest endfunc  
    • nooneclose
      By nooneclose
      Hey. I'm working on a new project and was wondering if there is a better way to "update" my Column E array. 
      Here is my code: 
      Local $nI  = 0                                                            ;Creates a name index of 0: nI = Name index Local $nII = 1                                                            ;Creates a name index of 1 for second loop: nII = Name Index 2 For    $iN = 0 To $IndexRows Step 1                                       ;Checks the roster for any names that appear twice      For $iN2 = 0 To $IndexRows Step 1          if $d_Names[$nI] == $d_Names[$nII] And $d_Names[$nII] <> "" Then              Local $timeSheetName = _ArraySearch($e_Names, $d_Names[$nI], 0, 0, 0, 0, 1)              ;MsgBox($MB_SYSTEMMODAL, "Found it", $d_Names[$nI] & " In column E on Row " & $timeSheetName)              Local $eI  = $timeSheetName + 1              ;ConsoleWrite($timeSheetName & @CRLF)              ;ConsoleWrite($eI & @CRLF)              ;ConsoleWrite(@CRLF)              _Excel_RangeInsert($OpenWorkbook.ActiveSheet, "E" & $eI & ":F" & $eI, $xlShiftDown)                                                                          ;Inserts a empty cell in columns E and F.              _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $d_Names[$nII], "E" & $eI)                                                                         ;Fills the empty cell in columns E with the doubled name              $aArray_Index = 2                                           ;Array element counter              For $Index = 2 To $IndexRows Step 1                        ;Loops through every row in the Excel file unto no rows are found or a null row is found                  $Array_Value_E = _Excel_RangeRead($OpenWorkbook, Default, "E"&$Index)                  $e_names[$aArray_Index] = $Array_Value_E                ;While the code loops every value in column E is stored in the E array (updating the array)                  $aArray_Index += 1              Next              ExitLoop          EndIf      Next      $nI  += 1      $nII += 1 Next Basically, It checks a roster for people whose name appears twice then inserts a new "row" for that person because they work in two different departments.
      I have to find that name however in Column E if two appear in column D. My code works but I think it is not as efficient as it could be. 
      Any ideas on how to improve the "update" for my array?
      (once it finds the double names in Column D it then searches for that name by going name by name in the Column E array and once it finds it inserts a new row. However, the E array doesn't have that new row stored in it so I have to "update" the array to properly find the next name)
      Any and all tips would be greatly appreciated. 
       
      NOTE: Just assume I'm opening the excel file properly please do not add that code in, it only complicates your answer. 
×