Checking if an xls is already open- how to?

Submitted by JXB on Fri, 09/19/2014 - 02:35
Forums: 
Journaling / NXOpen API

to all

I am testing opening a user supplied excel spreasheet. The user selects an excel document from a GUI and the macro read a lot of data from it to be process. I am using the following code (which works fine)

Dim objWorkbook = objExcel.Workbooks.Open (FileName:=xlFileNameToOpen, ReadOnly:=False)

I am trying to check if this xls specified is already opened. What is the best of doing this?

Thanks

Regards

JXB

NXJournaling - Fri, 09/19/2014 - 07:07

The following code will return True if the file is in use and False if not.





Function IsExcelFileOpen(ByVal fileName As String) As Boolean

Try
Using reader As New IO.BinaryReader(IO.File.Open(fileName, IO.FileMode.Open))
End Using

Catch ex As System.IO.IOException
'file already open
Return True
Catch ex As Exception
'other error
MsgBox(ex.Message, MsgBoxStyle.OkOnly, ex.GetType.ToString)
Return True
End Try

Return False

End Function

JXB - Sun, 09/21/2014 - 08:36

Did some Google search and found this. No test it yet in a NX journal

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

Determine if a File is open

The following routine checks to see if a file has been opened/locked:


Option Explicit

'Purpose : This function checks to see if a file is open or not
'Inputs : sFilePathName The file and path name of the file eg. C:\book1.xls
'Outputs : Returns True if the file is open else returns False
'Notes : This will return False if the file has not been open exclusively.
' eg. Opening a text file in a text editor will not lock the file and hence
' this routine will still return False.
'Revisions :

Function FileIsOpen(sFileName As String) As Boolean
Dim iFileNum As Integer, lErrNum As Long

On Error Resume Next
iFileNum = FreeFile()
'Attempt to open the file and lock it.
Open sFileName For Input Lock Read As #iFileNum
Close iFileNum
lErrNum = Err.Number
On Error GoTo 0

'Check to see which error occurred.
Select Case lErrNum
Case 0
'No error occurred.
'File is NOT already open by another user.
FileIsOpen = False

Case 70
'Error number for "Permission Denied."
'File is already opened by another user.
FileIsOpen = True

Case 53
'File not found
FileIsOpen = False

Case Else
'Another error occurred.
FileIsOpen = True
Debug.Print Error(lErrNum)
End Select

End Function

Thanks
Regards

JXB - Fri, 09/19/2014 - 07:35

Would never had got that
Regards
JXB

Thanks
Regards