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.