More then 1 Excel object running at same time , how can I refer to right one in code?

[Dave Lauberts]

Dear All

I have an access application which opens an new excel application in the
background ( Set ExcelApp = New Excel.Application) and does it 'stuff' no
problem, to keep down the code lines I have use the same 'Public Sub'
proceedure with different values to get data from the excel spreadsheet in
the background.

when I send it to the 'Public Sub' proceedure I use
Set ExcelApp = GetObject(, "Excel.Application")
to refer to the open excel application.

This works OK when I only have 1 instance of excel working. BUT if I already
have excel open with another spreadsheet it does not work ( I think it looks
at the Excel application already open and not the one I have created in the
background).

I don't want to put code in the application to close any excel applications
before I run my application - does anybody know how I can point to the right
Excel application?

Look forward to hearing from you all
Regards
Dave

 

[AA2e72E]

I have come to the conclusion that there is no way to choose a particular
instance of Excel (from several) to grab wit GetObject: I think it grabs the
last session created.

I would like to be proven wrong on this.

 

[Dave Lauberts]

Just a quick note - I have tried inserting the pathway to the spreadheet in
'GetObject' ie
Set ExcelApp = GetObject(strPathway, Excel.Application)
but but get an error message that 'The ActiveX componant can't create
object' - (possibly since the spreadsheet is already open?)

Have temp got round this by opening a further new excel application in the
Public Sub proceedure and opening the spreadsheet again, however this slows
the application down as it opens the new application.

It works OK but there must be a way to get at the original excel object -
appreciate it if somebody could point the way.

Regards
Dave

 

[NickHK]

Dave,
You do have the original Excel object that you are using; it is referenced
by "ExcelApp". To ensure you are using this instance, make sure all you code
refers to
ExcelApp.Workbooks("YourWB")........

NickHK

 

[Dave Lauberts]

Thanks Nick

You pointed me in the right direction.
In the second procedure I modified the code to
..
..
..
Dim ExcelWorkbook As Excel.Workbook
Set ExcelWorkbook= GetObject("myWBK.xls")
With ExcelWorkbook.ActiveSheet

 

[Stephen Bullen]

Hi Dave,

I have an access application which opens an new excel application in the
background ( Set ExcelApp = New Excel.Application) and does it 'stuff' no
problem, to keep down the code lines I have use the same 'Public Sub'
proceedure with different values to get data from the excel spreadsheet in
the background.

The way to do this is to either (a) declare ExcelApp as a global variable, do
the 'Public sub' doesn't need to set it to anything:



Dim ExcelApp As Excel.Application

Sub Caller()

Set ExcelApp = New Excel.Application

DoSomething ExcelApp
End Sub

Public Sub DoSomething()

With ExcelApp.ActiveSheet
...
End With
End Sub


Or pass the instance into the 'public sub':

Sub Caller()

Dim ExcelApp As Excel.Application

Set ExcelApp = New Excel.Application

DoSomething ExcelApp
End Sub

Public Sub DoSomething(ByRef ExcelApp As Excel.Application)

With ExcelApp.ActiveSheet
...
End With
End Sub

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

 

[Dave Lauberts]

sorry for repeat , my reply must have been direct to Nick and not to the
group!
Thanks Nick

You pointed me in the right direction.
In the second procedure I modified the code to
..
..
..
Dim ExcelWorkbook As Excel.Workbook
Set ExcelWorkbook= GetObject("myWBK.xls")
With ExcelWorkbook.ActiveSheet

 

[Dave Lauberts]

Hi
only way i could get it to work was to ask it to get the workbook

Dim ExcelWorkbook As Excel.Workbook
Set ExcelWorkbook= GetObject("myWBK.xls")
With ExcelWorkbook.ActiveSheet
Dave