Check if a excel file is open

[dzogchen]

Good morning,

Can anyone help me to with this code? I developed a code as a test to check if a excel file is open. The file is "Livro2.xlsx" as the code seems to work but when the file is closed and the code opens the file it suddendly closes it, and I want that the file stays opened.

Sub Livro2()
Dim objExcel As Object

On Error Resume Next
Set objExcel = GetObject("Livro2.xlsx")

If Err.Number <> 0 Then
MsgBox "File Open"

Else

MsgBox "file closed"
Set objExcel = CreateObject("Excel.Application")
Set objExcel = objExcel.Workbooks.Open("C:\Users\njesus\Documentos\Livro2.xlsx")
objExcel.Application.Visible = True

End If
End Sub

Best regards

[Agent Smith]

I use this to open excel files within access. Should hopefully be the same. You may need to set reference library.

    Dim oXLApp2 As Excel.Application      'Declare the object variables    
   Dim oXLBook2 As Excel.Workbook
    Set oXLApp2 = New Excel.Application   'Create a new instance of Excel
    Set oXLBook2 = oXLApp2.Workbooks.Open(smslog) 'Open an existing workbook
    oXLApp2.Visible = True
    oXLBook2.Activate
    oXLApp2.Application.WindowState = xlMaximized
'    AppActivate (oXLApp2)
    AppActivate "Excel"
    Set oXLBook2 = Nothing
    Set oXLApp2 = Nothing

[dzogchen]

Hi Agent Smith!

Thank you for the reply but I got an error running your code from word "Compile error" in the first line "oXLApp2 As Excel.Application". It seems the code is different for word.

Thank you anyway.

Best wishes

[gmayor]

The following will establish if the file exists and is opened by yourself or by another user and reports accordingly. If the file exists and is not open it is opened. The example below simply reports what is in cell A1 of Sheet1 to demonstrate that it works. If my messages don't make sense, blame Google .

Sub Livro3()
'Graham Mayor - https://www.gmayor.com - Last updated - 07 Jun 2019 
Dim xlApp As Object
Dim xlBook As Object
Dim FSO As Object
Const strWB As String = "Livro2.xlsx"
Const strPath As String = "C:\Users\njesus\Documentos\"
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If Err Then
        Set xlApp = CreateObject("Excel.Application")
    End If
    xlApp.Visible = False
    Set xlBook = xlApp.workbooks(strWB)
    On Error GoTo 0
    If xlBook Is Nothing Then
        If Not IsWorkBookOpen(strPath & strWB) = True Then
            Set FSO = CreateObject("Scripting.FileSystemObject")
            If FSO.FileExists(strPath & strWB) Then
                Set xlBook = xlApp.workbooks.Open(strPath & strWB)
                MsgBox xlBook.sheets(1).Range("A1")
            Else
                MsgBox strPath & strWB & vbCr & "Não encontrado!"
            End If
        Else
            MsgBox "O arquivo está em uso por outro usuário."
        End If
    End If
lbl_Exit:
    Set xlApp = Nothing
    Set xlBook = Nothing
    Set FSO = Nothing
    Exit Sub
End Sub

Private Function IsWorkBookOpen(FileName As String)
'https://stackoverflow.com/questions/9373082/detect-whether-excel-workbook-is-already-open#9373914
'with slight modification by Graham Mayor - https://www.gmayor.com - Last updated - 07 Jun 2019 
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: IsWorkBookOpen = False
    End Select
lbl_Exit:
    Exit Function
End Function

[dzogchen]

Thank you alot gmayor!! It worked! I have just changed the condition "xlApp.Visible = False" to True and disabled the "MsgBox xlBook.sheets(1).Range("A1")"

Any ideas to put the excel file as maximized window because it appears as a thin minimized window? I have tested the code with "xlBook.Application.WindowState = xlMaximized" next to "Set xlBook = xlApp.workbooks(strWB)" and it didn't worked.

Thank you

[Agent Smith]

Hi Agent Smith!

Thank you for the reply but I got an error running your code from word "Compile error" in the first line "oXLApp2 As Excel.Application". It seems the code is different for word.

Thank you anyway.

Best wishes

You need to go to tools / references and the select the Microsoft Excel XX.X object library in the vba editor. I thinks it's early vs late binding depending on what you want to use.

[dzogchen]

You need to go to tools / references and the select the Microsoft Excel XX.X object library in the vba editor. I thinks it's early vs late binding depending on what you want to use.

Thank you for the information!!

Thank you alot gmayor!! It worked! I have just changed the condition "xlApp.Visible = False" to True and disabled the "MsgBox xlBook.sheets(1).Range("A1")"

Any ideas to put the excel file as maximized window because it appears as a thin minimized window? I have tested the code with "xlBook.Application.WindowState = xlMaximized" next to "Set xlBook = xlApp.workbooks(strWB)" and it didn't worked.

Thank you

I have figured it out already!

in the excel file I put:

Private Sub Workbook_open()

Application.WindowState = xlMaximized


End Sub

Thank you all for the Help

Regards

[gmayor]

The macro I posted uses late binding to Excel and so the command xlMaximized is not valid.
You don't need to add a reference to Excel. You need to use instead the numeric equivalent of xlMaximized which is -4137

xlMaximized	-4137	Maximized
xlMinimized	-4140	Minimized
xlNormal	-4143	Normal

[dzogchen]

Thank you gmayor for the information, one more I have learned.