Excel automation: How to access XL sheet as the second task?

[Jack]

Hello,
I have a technical problem how to read from XL sheet in the second
task.
Let assume there is Excel spreadsheet already opened on desktop.
My code:
Public WithEvents moExcelApp As Excel.Application
Public WithEvents moExcel2ndTask As Excel.Application

Set moExcelApp = GetObject(, "Excel.Application")
If moExcelApp Is Nothing Then
Set moExcelApp = CreateObject("Excel.Application")
End If
If moExcelApp Is Nothing Then
Debug.Print "Excel app NOT found!"
ExcelApp = 1: Exit Sub
Else
moExcelApp.EnableEvents = True
Set moExcel2ndTask = CreateObject("Excel.Application")
moExcel2ndTask.EnableEvents = True
End If
==================
In Task Manager I can see the second Excel task running.
But it is not bound to the spreadsheet already opened, like moExcelApp is.
For example:
moExcel2ndTask.Workbooks.Count returns 0
but
moExcelApp.Workbooks.Count returns 1.

What should be done to bind the second Excel task to the already opened
spreadsheet?
What am I doing wrong?
What I need is to be able to read the cell values in the Excel second task,
while not to disturb user using the sheet in primary task (just to avoid
errors while sheet is in edit mode).
Your thoughts appreciated,
Jack

 

[Larry Serflaten]

What should be done to bind the second Excel task to the already opened
spreadsheet?
What am I doing wrong?

GetObject returns an already running object, while CreateObject starts off a
new process. If you want them both to bind to the same object, then you'd
just set the second variable to equal the first.

What I need is to be able to read the cell values in the Excel second task,
while not to disturb user using the sheet in primary task (just to avoid
errors while sheet is in edit mode).
Your thoughts appreciated,

I don't do that sort of programming, but it was somewhat obvious that
to get at the data in the sheet you'd need to access the desired sheet,
or a copy of it. Instead of creating a new application object, might
you try creating a copy of the original sheet, within the running application?

LFS

 

[Tim Williams]

You can't have the same workbook open in two different instances of Excel.

Tim

 

[Peter T]

Hello,
I have a technical problem how to read from XL sheet in the second
task.
Let assume there is Excel spreadsheet already opened on desktop.
My code:
Public WithEvents moExcelApp As Excel.Application
Public WithEvents moExcel2ndTask As Excel.Application

Set moExcelApp = GetObject(, "Excel.Application")
If moExcelApp Is Nothing Then
Set moExcelApp = CreateObject("Excel.Application")
End If
If moExcelApp Is Nothing Then
Debug.Print "Excel app NOT found!"
ExcelApp = 1: Exit Sub
Else
moExcelApp.EnableEvents = True
Set moExcel2ndTask = CreateObject("Excel.Application")
moExcel2ndTask.EnableEvents = True
End If
==================
In Task Manager I can see the second Excel task running.
But it is not bound to the spreadsheet already opened, like moExcelApp is.
For example:
moExcel2ndTask.Workbooks.Count returns 0
but
moExcelApp.Workbooks.Count returns 1.

What should be done to bind the second Excel task to the already opened
spreadsheet?
What am I doing wrong?
What I need is to be able to read the cell values in the Excel second task,
while not to disturb user using the sheet in primary task (just to avoid
errors while sheet is in edit mode).
Your thoughts appreciated,
Jack

If I follow want you're trying to do, or rather guessing with difficulty, it
seems like an incredibly convoluted way of catering for the possibility a
cell is in edit mode (or some other user dialog is displayed) while you are
trying to read cells. In that scenario you'll get an automation error of
the range object after a timeout of typically 5-10 seconds. As you can't be
sure of what cell data exists during that period there's not much else you
can do, other roll in a loop or try again later. (A read-only file in
another instance may be out of date).

Let assume there is Excel spreadsheet already opened on desktop.

How is that assumed, but let's say it is - there's no guarantee GetObject -
Excel will reference the instance with your file, unless there's only one
running instance.

Regards,
Peter T

 

[Jack]

You are right, Peter.
I am pursuing that on the user request.
It would be much simpler for me if my code app was capable of receiving
notification (error of some kind) when the accessed spreadsheet is in edit
mode.
How to get that error?
Thanks,
Jack

 

[Jack]

Another question.
When running Excel in the second task.
Initially there are 2 identical spreadsheets.
In one, user may do some changes, in the second my app will log in some
entries.
How to combine that data into one sheet on exit?
Your thoughts appreciated,
Jack

 

[Peter T]

It would depend on many factors relating to your overall scenario which is
far from clear. Eg, do you maintain long term references to the excel
instance, workbook, worksheet and range objects or are you starting from
scratch (to get the instance and cell data etc while in edit mode), and what
are you doing overall.

After a time out you will probably get error &H80010001 (-2147418111) on
trying to get any of those object references. Either give up immediately, or
loop while attempting to get the cell data accepting that error, but
eventually give up in case user has abandoned Excel in edit mode.

If you are testing in the VB6 VBE you may get the "not responding - switch
to" message, which is not helpful. FWIW might be easier to test in say Word
VBA, not sure.

Regards,
Peter T

 

[Peter T]

When asking, try and imagine the person reading your question doesn't know
what you have, where it is, what you are doing, etc, eg

How to combine that data into one sheet on exit?

In which of the two wb's, or copied sheet into some other (new) wb, on exit
of which wb in which instance, etc

Oh, and not least why?

Regards,
Peter T