How to check if my Workbook (excel) is open from a word document?

Question

Exactly I would like to check if my Excel Workbook (Only one) is open. IF it is open THEN use some code to get some data from it ELSE open my Excel file first and get some data from it. I would like to keep my Excel file always open as long as I am working on my word document.

Here is some code in a word document to try to achieve this:

Dim appExcel As Excel.Application
Dim xlWBook As Excel.Workbook

On Error Resume Next
Set appExcel = GetObject(, "Excel.Application")
If appExcel Is Nothing Then
    Set appExcel = CreateObject("Excel.Application")
    Set xlWBook = appExcel.Workbooks.Open(ActiveDocument.Path & strFile)
EndIf

' Some code
xlWBook.Sheets(3).Cells(4,2).value = strData1
' or the other way
strData2 = xlWBook.Sheet(4).Cells(3,5).Value
'    ...etc...

xlWBook.Close SaveChanges:=True
appExcel.Quit
Set xlWBook = Nothing
Set appExcel = Nothing

Do I need to close it (as above), knowing I will use this kind of code for another module or userform? Am I on the right track? Or is it a better practice to open it each time and then close it when you finished?

My goal is to avoid opening and closing my Excel file each time I need something from it. Note that I open my Excel file in the beginning via a click on a button in the ribbon to enter data directly on it, therefore the user is likely to leave it open, but he may close it.

A last thought: should I found out if the excel file is open? If yes close it, then open and close it each time I need it.

Thanks.

You could repurpose the answers [here](https://stackoverflow.com/questions/9373082/detect-whether-excel-workbook-is-already-open), or [here](https://stackoverflow.com/questions/46708491/excel-vba-checking-if-workbook-is-opened-and-opening-it-if-not). — BigBen, Apr 10 '20 at 16:56
If the file is already open, I see no need to close and reopen it. — BigBen, Apr 10 '20 at 17:01

FaneDuru · Accepted Answer · 2020-04-11T17:21:26.397

1

Try this way, please:

Sub CheckOpenWbinEccel()
  Dim appExcel As Excel.Application, boolFound as Boolean
  Dim xlWBook As Excel.Workbook, wb  As Excel.Workbook

On Error Resume Next
Set appExcel = GetObject(, "Excel.Application")
If appExcel Is Nothing Then
    Err.Clear: On Error GoTo 0 'it is good to clear the error and make VBA raise other errors, if the case
    Set appExcel = CreateObject("Excel.Application")
    Set xlWBook = appExcel.Workbooks.Open(ActiveDocument.path & strFile)
Else
    On Error GoTo 0' make VBA raise other errors, if the case
    For Each wb In appExcel
        If wb.FullName = ActiveDocument.path & strFile Then
            Set xlWBook = wb: boolFound = True: Exit For' no need to close and reopen it
        End If
        If Not boolFound Then Set xlWBook = appExcel.Workbooks.Open(ActiveDocument.path & strFile)
    Next
End If

' Some code
xlWBook.Sheets(3).Cells(4, 2).value = strData1
' or the other way
strData2 = xlWBook.Sheet(4).Cells(3, 5).value
'    ...etc...
  'if you want keeping Excel session and workbook open, comment the next two lines:
  xlWBook.Close SaveChanges:=True 'here
  appExcel.Quit                   'and here
  Set xlWBook = Nothing
  Set appExcel = Nothing
End Sub

edited Apr 11 '20 at 17:21

answered Apr 10 '20 at 17:02

FaneDuru

38,298
4
19
27

You're missing the case where Excel is open but the sheet is not found. – HackSlash Apr 10 '20 at 17:12
@HackSlash: Where, in the question or in the code, is written something about a "not found sheet"? "My goal is to avoid opening and closing my Excel file each time I need something from it."... I suppose that, if the workbook is open, the needed sheets must be there. Anyhow, such a check is easy to be done. – FaneDuru Apr 10 '20 at 17:26
Perfect, it does the job. Thanks. – JLuc01 Apr 10 '20 at 20:06
@FaneDuru, I misspoke, I meant Workbook. Your code looks for a workbook but if it doesn't find it the code just continues until it gets a null reference. You need something after your For Each like `If xlWBook Is Nothing Then Set xlWBook = appExcel.Workbooks.Open(ActiveDocument.path & strFile)` – HackSlash Apr 10 '20 at 20:28
@HackSlash: You are right. Thanks! Adapted the code to take care of that aspect, too... – FaneDuru Apr 11 '20 at 09:06

How to check if my Workbook (excel) is open from a word document?

1 Answers1