Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
More then 1 Excel object running at same time , how can I refer to right one in code?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
More then 1 Excel object running at same time , how can I refer to
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
More then 1 Excel object running at same time , how can I refer to right one in code?
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 "Dave Lauberts" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
More then 1 Excel object running at same time , how can I refer to right one in code?
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" wrote in message ... 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 "Dave Lauberts" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
More then 1 Excel object running at same time , how can I refer to right one in code?
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
More then 1 Excel object running at same time , how can I refer to right one in code?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Possible to refer to a sheet's object name ? | Excel Worksheet Functions | |||
make camera object refer to dynamic range | Excel Discussion (Misc queries) | |||
How to refer to a cell format code? | Excel Discussion (Misc queries) | |||
refer to the sheets Name property in my code (not what is on the Tab) | Excel Programming | |||
Excel Object in VB when running under scheduler control | Excel Programming |