don't open workbook if it is already open

[megatronixs]

Hi all,

Today I have a voodoo day where all is hard to fix or find answer.
at the beginning I had only code to open one excel workbook and do something with it. Now because of new process, I have to do something more to it, so if the workbook is open, use it and don't open another.
I can't seem to find answer for this.

This below works, but when I need to run the second part via other button, it will open the workbook again and that one will be empty with not the values from previous code run:

    Set objXL = CreateObject("Excel.Application")
    
    objXL.Visible = True
    Set xlWB = objXL.Workbooks.Open("F:\Test\Downstream.xlsm")
    Set xlWS = xlWB.Worksheets("process")
     
    LastRow = xlWS.Cells(60000, 1).End(xlUp).Row
  
    For j = 2 To LastRow
    Acc = xlWS.Cells(j, 1).Value

Any idea to solve this? by the end of the day I seem not to get it.

Greetings.

 

[Gasman]

Not sure you can do that unless you are the one who has it open?, and then you should know.?

I've found this out in the past when I still had the Excel workbook open in Excel to check something and then was trying to open it in my access code (for update admittedly).

HTH

 

[megatronixs]

I'm actually the only one having it open with the first part of the code works. Just want to continue with it without opening it again

Greetings.

 

[Solo712]

Hi all,

Today I have a voodoo day where all is hard to fix or find answer.
at the beginning I had only code to open one excel workbook and do something with it. Now because of new process, I have to do something more to it, so if the workbook is open, use it and don't open another.
I can't seem to find answer for this.

This below works, but when I need to run the second part via other button, it will open the workbook again and that one will be empty with not the values from previous code run:

    Set objXL = CreateObject("Excel.Application")
    
    objXL.Visible = True
    Set xlWB = objXL.Workbooks.Open("F:\Test\Downstream.xlsm")
    Set xlWS = xlWB.Worksheets("process")
     
    LastRow = xlWS.Cells(60000, 1).End(xlUp).Row
  
    For j = 2 To LastRow
    Acc = xlWS.Cells(j, 1).Value

Any idea to solve this? by the end of the day I seem not to get it.

Greetings.

Hi you should use "GetObject" first which will refer to an existing instance of Excel and create the object only if none is found. Like this:

On Error Resume Next
    Set objXL = GetObject(, "Excel.Application")
    
    If Err.Number <> 0 Then
        Err.Clear
        'MsgBox "creating new Excel"
        Set objXL = CreateObject("Excel.Application")
    End If

Then you should not have the issue of multiple instances of the same workbook.

Best,
Jiri

 

[Galaxiom]

First get any Excel objects as jiri shows.

Then loop through their Workbooks Collections and test the FullName property for a match.