Consulting

Results 1 to 9 of 9

Thread: Check if a excel file is open

  1. #1
    VBAX Regular
    Joined
    Apr 2007
    Posts
    27
    Location

    Check if a excel file is open

    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
    Microsoft 2010 | VBA 7.1

  2. #2
    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

  3. #3
    VBAX Regular
    Joined
    Apr 2007
    Posts
    27
    Location
    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
    Microsoft 2010 | VBA 7.1

  4. #4
    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
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  5. #5
    VBAX Regular
    Joined
    Apr 2007
    Posts
    27
    Location
    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
    Microsoft 2010 | VBA 7.1

  6. #6
    Quote Originally Posted by dzogchen View Post
    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.

  7. #7
    VBAX Regular
    Joined
    Apr 2007
    Posts
    27
    Location
    Quote Originally Posted by Agent Smith View Post
    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!!


    Quote Originally Posted by dzogchen View Post
    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
    Microsoft 2010 | VBA 7.1

  8. #8
    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
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  9. #9
    VBAX Regular
    Joined
    Apr 2007
    Posts
    27
    Location
    Thank you gmayor for the information, one more I have learned.
    Microsoft 2010 | VBA 7.1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •