Determine of Workbook is open already (from Word VBA)

[Robie]

Hi

I have a macro in Excel that I call from Word to get a looked up value. It works fine except I get multiple instances of Excel for each open of the workbook.

I use the following syntax to open the Workbook:

Code: Select all

Function GetFolderFromExcelList(sPrdName As String) as string

     Dim XL As Object
     Dim sFromExcel As String

     Set XL = CreateObject("Excel.Application")

     XL.Workbooks.Open "P:\ReleaseRequest to Excel\Release Requests Reports.xlsm"
     XL.Run "GetStuffFromExcel", sPrdName , sFromExcel 
    GetFolderFromExcelList= sFromExcel
End function

The problem I have is that I call this function *several* times and therefore it load Excel workbook for each *call*. Is there a way I can check if the Workbook is already loaded then all I do is call the function to get the information I need & not open the workbook again (& again & again & ...)? I want to keep the Workbook open if it is open already and therefore can't close it after every call to this function.

Thanks.
Robie

[HansV]

Change the line

Code: Select all

     Set XL = CreateObject("Excel.Application")

to

Code: Select all

    On Error Resume Next
    Set XL = GetObject(Class:="Excel.Application")
    On Error GoTo 0
    If XL Is Nothing Then
        Set XL = CreateObject(Class:="Excel.Application")
    End If

GetObject tries to get a reference to an already running instance of Excel. If that fails, we use CreateObject to start a new instance.

[Robie]

Change the line

Code: Select all

     Set XL = CreateObject("Excel.Application")

to

Code: Select all

    On Error Resume Next
    Set XL = GetObject(Class:="Excel.Application")
    On Error GoTo 0
    If XL Is Nothing Then
        Set XL = CreateObject(Class:="Excel.Application")
    End If

GetObject tries to get a reference to an already running instance of Excel. If that fails, we use CreateObject to start a new instance.

Excellent Hans. Thank you do much.