Check for open excel workbook and if open take control (1 Viewer)

[Gasman]

Same happens if you use CreateObject

Perhaps test if the temp file for that file exists?

Set fs = CreateObject("Scripting.FileSystemObject")
? fs.fileexists("F:\Temp\~$GCD.xlsm")
True

 

[CJ_London]

I found an alternative method that works for me here

Detect whether Excel workbook is already open

In VBA, I opened an MS Excel file named "myWork.XL" programmatically. Now I would like a code that can tell me about its status - whether it is open or not. I.e. something like IsWorkBookOpened("m...

stackoverflow.com

I haven't spent time working out what cells the code in post#11 is actually clearing - my example just clears a table, leaving the headers, but can easily be adapted to minimise the number of rows to be updated (e.g. sheet1$A1:B5 or sheet1$B10: G45) and using field names be selective as to which columns are to be cleared

And there is no error handling in the event the file does not exist

my code is

Function test()
Dim xlApp As Object
Dim xlwb As Object
Dim db As DAO.Database
Dim fName as string

    Set db = CurrentDb
    fName = "D:\Dev\ltexttest.XLSX"

    db.Execute ("UPDATE (SELECT * FROM [sheet1$A:B] AS xlData IN '" & fName & "'[Excel 12.0;HDR=yes;IMEX=0;ACCDB=Yes])  AS XL SET XL.test = '', XL.dummy = ''", dbFailOnErrror)
    db.Close

    If Not IsWorkBookOpen(fName) Then 'file not open, so open it
        Set xlApp = CreateObject("excel.application")
        xlApp.Visible = True
        Set xlwb = xlApp.Workbooks.Open(fName)
    End If

End Function

Function IsWorkBookOpen(FileName As String) As Boolean
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: Error ErrNo
    End Select

End Function

 

[The_Doc_Man]

CJ's method attempts to take an exclusive read lock on the file. The attempt fails if the file is already open (because in that case you can't have an exclusive lock of any flavor.) This is a "proper" use of file locking, by means of detecting lock incompatibility. That is, the requested lock was not compatible with the extant lock. Looking for a "temp" file might work via the FSO .FileExists method but does require you to guess what the file would be called and where it would be located.

I'm going to GUESS that if your GetObject actually finds the object and it is currently unowned, Access tries to make it yours from an object-owner viewpoint - but you still would have trouble connecting to its control channels. From what I could find last time I looked, what happens when you open one of those app objects, the app opens but its input and output channels are opened as though you were using a network connection, a "socket" between the created application object and the task created to hold the executable image you just implicitly launched. The problem is that TCP/IP has some rules about socket reconnects and I think they might sometimes get in the way. If your creator image vanishes, what you have is a "dangling" application object's task with disconnected sockets. Finding the object is easy enough. Connecting to it? Not so much. Offhand, I don't know of a way to do that because it depends on the way the connection was opened and I don't think we have control over that.

 

[ebs17]

Check for open excel workbook and if open take control

The first code in your first post has two problems:
- Access doesn't know any Workbooks listing - except with an additional reference to the Excel instance to be used.
- Multiple instances of Excel can be open, especially when opened manually. So you would have to examine the correct instance for the workbook.

Steps:
- The Excel file should exist.
- The path and name of the workbook should be known.
If necessary, this must be checked and determined.

Test on file is open as shown, attempting exclusive access to this file:

' ©2002 by Jost Schwider, http://vb-tec.de/
Public Function IsFileOpen(ByRef Path As String) As Boolean
  Dim FileNr As Integer
  Dim ErrorNr As Long

  'Datei testweise öffnen:
  On Error Resume Next
    FileNr = FreeFile
    Open Path For Input Lock Write As #FileNr
      ErrorNr = Err.Number
    Close #FileNr
  On Error GoTo 0

  'Ggf. Fehler verarbeiten:
  Select Case ErrorNr
  Case 0    'kein Fehler:
    'NOP
  Case 70   'Permission denied':
    IsFileOpen = True
  Case Else 'sonstiger Fehler:
    Err.Raise ErrorNr
  End Select
End Function

Subsequent step: Access to the Excel instance in which the workbook is open.

' Late Binding
Dim oExcel As Object
Dim oWorkbook As Object

Set oWorkbook = GetObject(FullPathWorkbook)
Set oExcel = oWorkbook.Application

You can now continue working freely with the references to the instance and workbook.