none
opening excel from access with vba when excel is already open RRS feed

  • Question

  • Hi,

    I have an access app and am trying to open an excel file. if excel is not already running, there is no problem. if excel is open, it correctly opens excel in a new instance but also tries to open the file as read only in the existing instance of excel. how do I stop this? my code is below. the access users have multiple versions of excel hence the late binding.

    ideally I wanted to check the following before opening the file:

    is the file open on the users pc? - if yes then activate it

    is the file open by someone on the network? - show a message that its open by user x

    otherwise open the file

    Public Sub openXL(strfile As String, Optional Vis As Boolean)
        On Error GoTo openXL_Error
    
        If Nz(strfile, "") = "" Then Exit Sub
    
        Dim xlApp As Object
    
        If IsWBOpen(strfile) = False Then
    'createNew:
        On Error Resume Next
        Set xlApp = GetObject("Excel.application")
        Debug.Print "creating new"
        If xlApp Is Nothing Then
        Set xlApp = CreateObject("Excel.Application")
        End If
        xlApp.Visible = True
        xlApp.Workbooks.Open strfile ', True, False
        End If
    checkvis:
        If xlApp Is Nothing Then GoTo LocalExit
    
        If Nz(Vis, False) = True Then
            xlApp.Visible = True
        Else
            xlApp.Visible = False
        End If
    
    LocalExit:
        On Error Resume Next
    
        Set xlApp = Nothing
        Exit Sub
    
    openXL_Error:
    
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure openXL of Module ExcelControl"
        GoTo LocalExit
    
    End Sub
    Function IsWBOpen(fileName As String)
        Dim ff As Long, ErrNo As Long
        IsWBOpen = False
        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: IsWBOpen = False
        Case 70: IsWBOpen = True
        Case Else: Error ErrNo
        End Select
    End Function


    edit I scrapped all the working out of whether it was open and used the application.followhyperlink(strfile) command instead now excel handles whether its open. it requires a few tweaks but fixes my issue
    • Edited by Nick Keyes 1 Thursday, October 12, 2017 3:03 PM found another solution
    Thursday, October 12, 2017 1:02 PM

All replies

  • Hi Nick,

    The first argument to the GetObject method is the filespec. Have you tried adding the filespec to see if you can "get" the open Excel file?

    For example:

    Set xlApp = GetObject(strfile)

    or

    Set xlApp = GetObject(strfile, "Excel.Application")

    Just a thought...



    • Edited by .theDBguy Thursday, October 12, 2017 2:42 PM
    Thursday, October 12, 2017 2:39 PM
  • Hi Nick Keyes 1,

    I try to test your code on my side.

    I see that if Excel is already open then also it creates a new Excel object.

    I can see that it is open the file in just newly created instance of Excel.

    and it is not read only.

    I can modify the file contents.

    I also try to check the task manager to see which files are opened in which Excel instances.

    I find that the file I try to open with your code is only get opened in new instance.

    so I suggest you to again try to test your code. if possible then you can try to test it on other machine to check the difference.

    let us know about your testing result. so that we can try to provide further suggestions if needed.

    Regards

    Deepak 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Friday, October 13, 2017 2:12 AM
    Moderator
  • Hi Nick Keyes 1,

    other thing I notice that you find a work around for your issue.

    you edit the original post to share the solution.

    so if your issue is solved then you should post a new reply, instead of editing the original post.

    if you edit the original post then no one get notification about it and no one knows that issue is solved now.

    so I suggest you to post a new reply with solution and mark it as an answer.

    so that we can close this thread.

    if you do not mark the answer then this thread will remains open.

    so take the appropriate action to close this thread.

    Thanks for your understanding.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, October 13, 2017 2:26 AM
    Moderator
  • Hi,

    >is the file open by someone on the network? - show a message that its open by user x

    I guess this question is of most interest.

    Try something like this to get what you aimed at:

    Sub ShowMessage(fullFileName As String)
        If IsFileOpened(fullFileName) Then
            MsgBox "The file is opened by " & BlockingUserName(fullFileName)
        Else
            MsgBox "The file isn't blocked"
        End If
    End Sub
    
    Function IsFileOpened(fileName As String) As Boolean
    Dim FSO As Object
        Set FSO = CreateObject("Scripting.FileSystemObject")
        IsFileOpened = FSO.FileExists(FSO.GetParentFolderName(fileName) & "\~$" & FSO.GetFileName(fileName))
        Set FSO = Nothing
    End Function
    
    Function BlockingUserName(fileName As String) As String
    Dim FSO As Object
    Dim File As Object
    Dim fileText As String
        Set FSO = CreateObject("Scripting.FileSystemObject")
        Set File = FSO.GetFile(FSO.GetParentFolderName(fileName) & "\~$" & FSO.GetFileName(fileName))
        File.Copy FSO.GetParentFolderName(fileName) & "\$" & FSO.GetFileName(fileName)
        
        Open FSO.GetParentFolderName(fileName) & "\$" & FSO.GetFileName(fileName) For Input As #1
        Dim s As String
        Input #1, fileText
        Close #1
        
        Set File = FSO.GetFile(FSO.GetParentFolderName(fileName) & "\$" & FSO.GetFileName(fileName))
        File.Delete
        Set File = Nothing
        Set FSO = Nothing
        
        BlockingUserName = Trim(Mid(fileText, 2, InStr(2, fileText, Left(fileText, 1), vbTextCompare) - 2))
    End Function


    • Proposed as answer by Sergiy_Vakshul Saturday, October 14, 2017 10:56 PM
    Saturday, October 14, 2017 12:37 AM