opening excel from access with vba when excel is already open

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

Answer 1

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

Answer 2

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 [e-mail].

Answer 3

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 [e-mail].

Answer 4

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