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

[wackywoo105]

I've been trying to adapt some code so I can take control of an open excel workbook.

Dim WB As excel.Workbook
Dim myWB As String

myWB = "LabOrderSheet.xlsx"

For Each WB In Workbooks
MsgBox WB.Name
    If WB.Name = myWB Then
        WB.Activate
        MsgBox "Workbook Found!"
        Exit Sub
    End If
Next WB

MsgBox "Not Found"

Set WB = Nothing

For some reason it worked on and off at first. Now it just doesn't detect the already open workbook at all?

Previously I would check if the workbook was open and require it to be closed, so as not to open it again, but I would like to be able to just take control of it if it is already open. I tried using the code as below:

If Dir(gdrive & "LabOrderSheet.xlsx") = "" Then
MsgBox "LabOrderSheet.xlsx sheet is missing."
Exit Sub
Else
'Ret = IsWorkBookOpen(gdrive & "LabOrderSheet.xlsx")
'If Ret = True Then
'    MsgBox ("LabOrderSheet sheet must be closed before running this. Please close it and try again.")
'    Exit Sub
'End If
End If

Dim oXLBook As excel.Workbook
Dim oXLSheet As excel.Worksheet
Dim oXLApp As excel.Application      'Declare the object variables

'https://excelchamps.com/vba/check-workbook-open/
Dim myWB As String

'myWB = InputBox(Prompt:="Enter the workbook name.")
myWB = "LabOrderSheet.xlsx"

For Each oXLBook In Workbooks
    If oXLBook.Name = myWB Then
        Set oXLApp = excel.Application
        oXLBook.Activate
        Set oXLSheet = oXLBook(oXLBook.Name).Worksheets(1)
        MsgBox "Workbook Found!"
        GoTo FoundIt
    End If
Next oXLBook

MsgBox "Not Found"

Set oXLApp = New excel.Application   'Create a new instance of Excel
Set oXLBook = oXLApp.Workbooks.Open(gdrive & "LabOrderSheet.xlsx") 'Open an existing workbook
Set oXLSheet = oXLBook.Worksheets(1)

FoundIt:

Set oXLSheet = oXLBook.Worksheets(1)

*/ DO STUFF ./*

oXLApp.DisplayAlerts = True
oXLApp.Application.EnableEvents = True
oXLApp.Visible = True
    oXLBook.Activate
    oXLApp.Application.WindowState = xlMaximized
    Set oXLSheet = Nothing
    Set oXLBook = Nothing
    Set oXLApp = Nothing

As the code doesn't recognise the already open excel it just opens the file again, which of course is read only and thus the title has read only in it.

Can anyone help with setting a oXLBook to an already open workbook and taking control of the corresponding excel application with oXLApp?

 

[Gasman]

I think you would have to kill the Excel instance that has that workbook open.

 

[cheekybuddha]

Are you doing this from Access or Excel?

If you are doing it from Access, there is a much simpler method to get an open workbook:

Function GetXLWrkBk(PathToFile As String) As Object
On Error Resume Next

  Set GetXLWrkBk = GetObject(PathToFile)

End Function

Then use it like:

Dim wb As Object

Set wb = GetXLWrkBk("C:\SomeFolder\YourFile.xlsx")
If wb Is Nothing Then
  ' File wasn't open
  ' Perhaps code to open it if you need
Else
  ' Do something with your workbook object
  MsgBox wb.Name
End If

 

[wackywoo105]

Sorry and thanks. This is from within Access.

I will give that a go.

EDIT: that does work but what I'm trying to do is if the workbook is open then take control and if not then open it, assuming it exists.

 

[The_Doc_Man]

Conceptually, there could well be an issue having to do with Windows security. If the session (task, process... take your pick of nomenclature) is still open that has the workbook open, you must kill the other session first. Otherwise, you cannot do anything because the file is locked by the opening session. If the session is still active, then Windows is enforcing process isolation - which is a rule that says sessions may not influence each other unless the nature of what they BOTH do allows it. This is an "anti-hacker" rule that has been around since the 1990s. File locking has been part of that session isolation since WinNT came onto the scene, when the Win kernel got rewritten to start tightening up security.

You mentioned hit-or-miss operation in the past, but lately no success. Windows patches have been closing holes for years, so there is no telling which holes got patched lately - particularly if it was an OFFICE patch rather than a WINDOWS patch. If this is a workbook opened by another session in exclusive mode (which for Excel is the default), you cannot touch it due to the file locks. The only way your process COULD have worked would be if the Excel program was opened by automation (i.e. an Access application object) that then failed to clean up after itself before exiting, thus leaving the Excel image running under automation, with no supporting session.

 

[wackywoo105]

when I say hit and miss I mean today. I only found that code a few hours ago. At first it worked. Then as I adapted it it stopped working. I went back to the version I found and it worked. then after a while even the original code wouldn't work at all.

I noticed an Excel process in task manager that I couldn't close (so restarted machine), so maybe this had something to do with it. Also some of the excel instance were opening as read only which appears to show in the title, so maybe this was confusing things, but again the read only versions were only opening because the code didn't detect the already open excel workbook.

So far I can check if a workbook exists. I can check if it's open already. I can open it if it isn't open and exists. The only thing I can't do is take control if it was opened manually, so in this case I prompt the user to close it first and then open it with code, which seems a bit daft and counterintuitive.

 

[cheekybuddha]

I noticed an Excel process in task manager that I couldn't close

Somewhere in your Excel automation code you will have an unqualified reference to and Excel object.

This will cause the hanging instance in Task manager.

Show all your code that involves Excel and we can try and help you identify the problem.

The only thing I can't do is take control if it was opened manually,

Did the code I suggested not do that?

 

[CJ_London]

What is your ‘do stuff’?

if to append new data or modify existing data you can use sql

however you cannot use sql to delete rows

and just to check, when checking if an excel file is open - I presume you mean opened by you and not another user?

 

[Gasman]

Conceptually, there could well be an issue having to do with Windows security. If the session (task, process... take your pick of nomenclature) is still open that has the workbook open, you must kill the other session first. Otherwise, you cannot do anything because the file is locked by the opening session. If the session is still active, then Windows is enforcing process isolation - which is a rule that says sessions may not influence each other unless the nature of what they BOTH do allows it. This is an "anti-hacker" rule that has been around since the 1990s. File locking has been part of that session isolation since WinNT came onto the scene, when the Win kernel got rewritten to start tightening up security.

You mentioned hit-or-miss operation in the past, but lately no success. Windows patches have been closing holes for years, so there is no telling which holes got patched lately - particularly if it was an OFFICE patch rather than a WINDOWS patch. If this is a workbook opened by another session in exclusive mode (which for Excel is the default), you cannot touch it due to the file locks. The only way your process COULD have worked would be if the Excel program was opened by automation (i.e. an Access application object) that then failed to clean up after itself before exiting, thus leaving the Excel image running under automation, with no supporting session.

Doc,
I tested that code from @cheekybuddha and I was able to update and save an open workbook?
The only thing I would say is that I was the opener of that workbook and the opener of the access DB accessing that workbook with that code.

Not sure the same would apply if another user had opened it?

However if the workbook is set to shared, perhaps that would not matter? That workbook has no need to be shared as it is only for me.

 

[cheekybuddha]

Ah yes, it might be a different story if another user (on another machine?) has opened the workbook.

Need @wackywoo105 to clarify the actual situation

 

[wackywoo105]

Thanks all. I will put the do stuff below. It just clears the form in this instance. @cheekybuddha sorry I tried your code but it reports the file name even if the excel workbook isn't open, hence I thought it was just checking to see if it exists. If it is capable of taking control if it's open, what happens if it isn't open? Does it open it?

Dim x As Integer
Dim y As Integer

For x = 1 To 17 Step 8
    For y = 1 To 17 Step 16

oXLSheet.Cells(y, x).Value = ""
oXLSheet.Cells(y + 1, x + 1).Value = ""  'OrderDate
oXLSheet.Cells(y + 2, x + 1).Value = ""  'Left(Firstname, 1) & " " & Surname & " " & OrderType
oXLSheet.Cells(y + 3, x + 2).Value = ""  'LensType

oXLSheet.Cells(y + 5, x + 1).Value = ""  'RESph
oXLSheet.Cells(y + 6, x + 1).Value = ""

oXLSheet.Cells(y + 5, x + 2).Value = ""  'RECyl
oXLSheet.Cells(y + 6, x + 2).Value = ""

oXLSheet.Cells(y + 5, x + 3).Value = ""  'REAxis
oXLSheet.Cells(y + 6, x + 3).Value = ""

oXLSheet.Cells(y + 5, x + 4).Value = ""  'REPrism
oXLSheet.Cells(y + 6, x + 4).Value = ""

oXLSheet.Cells(y + 5, x + 5).Value = ""  'REBase
oXLSheet.Cells(y + 6, x + 5).Value = ""


oXLSheet.Cells(y + 5, x + 6).Value = ""  'REAdd
oXLSheet.Cells(y + 6, x + 6).Value = ""
oXLSheet.Cells(y + 7, x + 1).Value = ""  'REDVOC
oXLSheet.Cells(y + 8, x + 1).Value = ""  'LEDVOC


oXLSheet.Cells(y + 7, x + 3).Value = ""  'REHT
oXLSheet.Cells(y + 8, x + 3).Value = ""  'LEHT
oXLSheet.Cells(y + 7, x + 4).Value = ""  'REDirection
oXLSheet.Cells(y + 8, x + 4).Value = ""  'LEDirection

oXLSheet.Cells(y + 9, x + 1).Value = ""  'Notes

oXLSheet.Cells(y + 12, x + 2).Value = ""  'FrameType
oXLSheet.Cells(y + 13, x + 2).Value = ""  'FrameDetails
oXLSheet.Cells(y + 14, x + 1).Value = ""  'FrameA
oXLSheet.Cells(y + 14, x + 3).Value = ""  'FrameDBL
oXLSheet.Cells(y + 14, x + 5).Value = ""  'FrameB

    Next y
Next x

 

[cheekybuddha]

If it is capable of taking control if it's open what happens if it isn't open? Does it open it?

Yes, it does appear to open it if it's not already open.

 

[Gasman]

Yes, it appears to open it, but then in my case, I am unable to view the workbook again.? from within Excel
Fortunately I have backup.

 

[wackywoo105]

So how do I go about editing it and then leaving it visible on screen? The following doesn't work.

Dim wb2 As Object

Set wb2 = GetObject(gdrive & "LabOrderSheet.xlsx")
If wb2 Is Nothing Then
MsgBox "no"
Else
  ' Do something with your workbook object
  MsgBox wb2.Name
  Set oXLSheet = wb2.Worksheets(1)
  oXLSheet.Cells(1, 1).Value = "hello"
End If

 

[The_Doc_Man]

Doc,
I tested that code from @cheekybuddha and I was able to update and save an open workbook?
The only thing I would say is that I was the opener of that workbook and the opener of the access DB accessing that workbook with that code.

Not sure the same would apply if another user had opened it?

However if the workbook is set to shared, perhaps that would not matter? That workbook has no need to be shared as it is only for me.

If the workbook was opened shared, it might be possible to update and save it. Opened exclusive? No. If you opened the workbook and from the same session opened Access to do an automation operation on the workbook, your parent session is the same for both child processes. If someone else opened the workbook (on a shared system in a network), the primary file lock is owned by the first session to open it. Then it becomes a matter of ownership and permissions and system-level privileges. To say that there is a hornet's nest in that pile just waiting to be whacked does not cover the complexity of the situation.

 

[Gasman]

To say that there is a hornet's nest in that pile just waiting to be whacked does not cover the complexity of the situation.

That is true.
I first tested with the file open. That appeared to work fine. It updated the workbook and saved it.
However if not open, it opens it, but then I cannot even see it when I open manually in Excel, despite it being there and when I go to close Excel it asks if I want to save any changes.

Then that happens no matter how many times I try and open it manually.
As the function is the Excel object I have no idea as how to get it visible?

 

[cheekybuddha]

@Gasman,

Perhaps try again:

Dim wb As Object

Set wb = GetObject("Path/To/GCD.xlsm")
wb .Close False
Set wb = Nothing

 

[cheekybuddha]

@wackywoo105

I seem to have the same problem in getting the workbook to show.

If you need to show it to the user then use the more traditional methods of opening a workbook where you can set the xlApp.Visible = True

I'll look up some code for you later, but I don't have the time right now.

 

[Gasman]

No, does the same as before.

Sub OpenWorkbook()
Dim wb As Object

Set wb = GetXLWrkBk("F:\Temp\GCD.xlsm")
If wb Is Nothing Then
  ' File wasn't open
  ' Perhaps code to open it if you need
Else
  ' Do something with your workbook object
  MsgBox wb.Name
  wb.Sheets("SF66OEK").Range("M1").Value = "Paul"
  wb.Save
  wb.Close False
End If
Set wb = Nothing

End Sub

 

[cheekybuddha]

I just read a stack overflow question (with no solution) where the questioner said that using GetObject directly hid their workbook, and they had to use Workbooks -> unhide to get it to show again.

Don't know where that is though, and not in front of computer