VBA open excel from word

Question

I'm opening excel file from word vba code and reading data from it.
I'm using the below code (copied from: https://wordmvp.com/FAQs/InterDev/ControlXLFromWord.htm)

My problem is as follows:
If no excel instance is active - the code works fine - reads data from exel and the excel stays closed.
If there is an excel file (different file) open - the code opens my file and doesn't close it - so at the end of the code I'm having 2 threads of excel

I tried to use "Application.ScreenUpdating = False" before the "oXL.Workbooks.Open..." line - but no success

What do I need to do so my file will be closed even if previous file of excel is already open???

Sub WorkOnAWorkbook()

Dim oXL As Object
Dim oWB As Object
Dim oSheet As Object
Dim oRng As Object
Dim ExcelWasNotRunning As Boolean
Dim WorkbookToWorkOn As String
Dim mydoc As Word.Document

'specify the workbook to work on
WorkbookToWorkOn = "C:\Users\yossi\Dropbox\Temp\STR\book1.xlsx"

'If Excel is running, get a handle on it; otherwise start a new instance of Excel
On Error Resume Next
Set oXL = GetObject(, "Excel.Application")

If Err Then
ExcelWasNotRunning = True
Set oXL = CreateObject("Excel.Application")
End If

On Error GoTo Err_Handler

'If you want Excel to be visible, you could add the line: oXL.Visible = True here; but your code will run faster if you don't make it visible
Application.ScreenUpdating = False

'Open the workbook
Set oWB = oXL.Workbooks.Open(FileName:=WorkbookToWorkOn)

oWB.Sheets("sheet1").Range("A1:B2").Copy

Documents(1).Activate
Selection.PasteAndFormat (wdFormatOriginalFormatting)

'Process each of the spreadsheets in the workbook
'For Each oSheet In oXL.ActiveWorkbook.Worksheets
'put guts of your code here
'get next sheet
'Next oSheet

If ExcelWasNotRunning Then
oXL.Quit
End If

'Make sure you release object references.
Set oRng = Nothing
Set oSheet = Nothing
Set oWB = Nothing
Set oXL = Nothing

'quit
Exit Sub

Err_Handler:
MsgBox WorkbookToWorkOn & " caused a problem. " & Err.Description, vbCritical, _
"Error: " & Err.Number
If ExcelWasNotRunning Then
oXL.Quit
End If

End Sub

Answer 1

Your code opens 2 instances of excel because the "GetObject" statement doesn't give an error when it runs while Excel is closed. Using the "CreateObject" only will be better for you as it runs while Excel is closed.