How to detect whether a excel file is openned..

[Rota]

Hi Dude,

I am currently operating on a Excel file using an Excel UDF, every time I open the file with "$oExcel.WorkBooks.Open($filepath, Default, False) " and end with "_ExcelBookClose($oExcel, 1, 0)" to close the work file, while every time when there is a error happen before it goes to "ExcelBookClose", the Excel file will be remain openning and occupy a certain of resource, I used to have a long long process list of openning EXCEL.exe in my Task Manager, so I am calling for your help, if there is any way we get to know if the specific excel work book is openning before running the core operation... such as:

-------------------------------

$filepath = "C:\ABC.XLS"

do until isopen($filepath)=0

Excelclose($filepath)

loop

--------------------------------

By then we can have these openning file close first then process next.

Please help....

Thanks a ton...

[rover]

Hi Rota

Here's an example of monitoring a file for open handles

script opens a file with exclusive access and fails if file already open by another application.

this code wont work with some programs and files that don't seem to keep an open file handle.

e.g. open a text file with a text editor (TextPad) and _FileInUse() reports no open handles

works fine with programs like Word and Excel etc.

Example

expandcollapse popup

Opt('MustDeclareVars', 1)
Local $file = "C:\ABC.XLS"
Local $bFileOpen
If Not FileExists($file) And ConsoleWrite("!FileExists error: " & $file & @crlf) Then Exit
While 1
    Sleep(10)
    $bFileOpen = _FileInUse($file, 1)
    If $bFileOpen = -1 And ConsoleWrite("!_FileInUse() error" & @crlf & '>Error code: ' & @error & @crlf) Then Exit
    ConsoleWrite('-FileInUse = ' & ($bFileOpen <> 0) & @crlf & '>Error code: ' & @error & @crlf)
    If Not $bFileOpen Then
        ConsoleWrite("! File: "& $file &" is not in use" & @CRLF)
        Exit
    EndIf
WEnd

;from this post:
;Need help with copy verification
;http://www.autoitscript.com/forum/index.php?showtopic=53994
;===============================================================================
;
; Function Name:    _FileInUse()
; Description:      Checks if file is in use
; Syntax.........: _FileInUse($sFilename, $iAccess = 1)
; Parameter(s):     $sFilename = File name
; Parameter(s):     $iAccess = 0 = GENERIC_READ - other apps can have file open in readonly mode
;                   $iAccess = 1 = GENERIC_READ|GENERIC_WRITE - exclusive access to file,
;                   fails if file open in readonly mode by app
; Return Value(s):  1 - file in use (@error contains system error code)
;                   0 - file not in use
;                   -1 dllcall error (@error contains dllcall error code)
; Author:           Siao
; Modified          rover - added some additional error handling, access mode
; Remarks           _WinAPI_CreateFile() WinAPI.au3
;===============================================================================
Func _FileInUse($sFilename, $iAccess = 0)
    Local $aRet, $hFile, $iError, $iDA
    Local Const $GENERIC_WRITE = 0x40000000
    Local Const $GENERIC_READ = 0x80000000
    Local Const $FILE_ATTRIBUTE_NORMAL = 0x80
    Local Const $OPEN_EXISTING = 3
    $iDA = $GENERIC_READ
    If BitAND($iAccess, 1) <> 0 Then $iDA = BitOR($GENERIC_READ, $GENERIC_WRITE)
    $aRet = DllCall("Kernel32.dll", "hwnd", "CreateFile", _
                                    "str", $sFilename, _ ;lpFileName
                                    "dword", $iDA, _ ;dwDesiredAccess
                                    "dword", 0x00000000, _ ;dwShareMode = DO NOT SHARE
                                    "dword", 0x00000000, _ ;lpSecurityAttributes = NULL
                                    "dword", $OPEN_EXISTING, _ ;dwCreationDisposition = OPEN_EXISTING
                                    "dword", $FILE_ATTRIBUTE_NORMAL, _ ;dwFlagsAndAttributes = FILE_ATTRIBUTE_NORMAL
                                    "hwnd", 0) ;hTemplateFile = NULL
    $iError = @error
    If @error Or IsArray($aRet) = 0 Then Return SetError($iError, 0, -1)
    $hFile = $aRet[0]
    If $hFile = -1 Then ;INVALID_HANDLE_VALUE = -1
        $aRet = DllCall("Kernel32.dll", "int", "GetLastError")
        ;ERROR_SHARING_VIOLATION = 32 0x20
        ;The process cannot access the file because it is being used by another process.
        If @error Or IsArray($aRet) = 0 Then Return SetError($iError, 0, 1)
        Return SetError($aRet[0], 0, 1)
    Else
        ;close file handle
        DllCall("Kernel32.dll", "int", "CloseHandle", "hwnd", $hFile)
        Return SetError(@error, 0, 0)
    EndIf
EndFunc

[Rota]

Super thanks Rover for your sharing..

I tested on your codes while it seems not working for the files on share mode, it will always turn to "File not in use.." for those share files... so how can we deal with this??

Thanks

[rover]

Super thanks Rover for your sharing..

I tested on your codes while it seems not working for the files on share mode, it will always turn to "File not in use.." for those share files... so how can we deal with this??

Thanks

Hi Rota

I assume you are using the Excel.au3 UDF by locodarwin and others.

can you post a script example?

can you clarify what you mean by 'share mode'?

do you mean the file currently opened by an instance of Excel.exe?

are you running the script with read/write access?

example _FileInUse($file, 1)

if the file is open then it returns 1 or True with an error message in the console of 32

meaning the script cannot open the file. (the script tries to open a file with no sharing)

if you get 'File not in use' then there is no open handle to the file by an instance of excel.

that will tell you if that workbook file is currently open, then next you need to deal with open instances of excel that need closing

after a script crash.

open file handles can be closed forcibly but its best to close Excel windows manually or with WinClose

I tried the excel UDF and opened an xls file without running _ExcelBookClose($oExcel, 0) afterwards

then closed excel manually, the open file handle was closed by excel, the _ExcelBookOpen() object was closed by AutoIt exit cleanup.

not having tried the UDF before I assumed it performed operations without running Excel in a window,

however now having run it for the first time I see it launches a normal Excel window.

so you need to close one or more instances of excel when your script crashes?

post a script example that exhibits this problem