programming issue when using Excel automation

[Jack]

Hello,
My application interacts with Excel spreadsheet. Periodically it
reads some data from the predefined sheet's column.
Everything works fine, when user does not use that spreadsheet at the same
time.
However, when user is typing in the sheet at the same time when my app wants
to read data from it there is a problem. Excel displays 'Component Busy'
message with the Switch to..., Retry and Cancel options.

How to solve that problem?
My app uses Excel automation and it connects to Excel using this code:

Public WithEvents moExcelApp As Excel.Application

Set moExcelApp = GetObject(, "Excel.Application")
If moExcelApp Is Nothing Then
Set moExcelApp = CreateObject("Excel.Application")
End If

Your comments appreciated,
Jack

 

[Peter T]

Indeed you'll get that massage trying to attach a reference to an Excel
instance when it is in edit mode (tying in a cell before pressing enter).

Following worked for me running in VB6 running from the IDE (having first
put Excel into edit mode):
-If Excel is not the active window bring it to front
- Sendkeys Escape

It means of course user might lose whatever was typed into some cell before
pressing Enter, in practice relatively rare.

Private Declare Function FindWindow Lib "user32" _
Alias "FindWindowA" ( _
ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long

Private Declare Function GetActiveWindow Lib "user32" () As Long

Private Declare Function SetForegroundWindow Lib "user32" ( _
ByVal hwnd As Long) As Long

Private moExcelApp As Object ' As Excel.Application


Private Sub Form_Click()
Dim bXLnotActive As Long
Dim hWinActive
Dim hXLwin As Long

hXLwin = FindWindow("XLMAIN", vbNullString)

If hXLwin = 0 Then
' no xl instance, use creatobject
Else

hWinActive = GetActiveWindow
bXLnotActive = hWinActive <> hXLwin
If bXLnotActive Then SetForegroundWindow hXLwin

Set moExcelApp = GetObject(, "Excel.Application")
SendKeys "{ESC}"

'if bXLnotActive then reactivate the original active window, maybe

End If

Print moExcelApp.Caption
End Sub

Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
Set moExcelApp = Nothing
End Sub

I spent a while fiddling around with the Sendkeys in advance of doing the
GetObject. Nothing worked, even using the keyboard event API instead of
SendKeys. It seems counter intuitive but putting SendKeys after the
GetObject seems to do the trick, at least it does for me. Not sure why
though and it might not be the best way.

Regards
Peter T

 

[Jack]

I do not understand your approach and how that can help me.
In my case I want to have both:
1. being able to pull data out of spreadsheet
and
2. do not interrupt user if he is using spreadsheet at the same time.

Is that possible?
Thanks,
Jack

 

[stefan onken]

hi Jack,
imho no.
you can use always CreateObject to open the workbook in a second task
and get data out of a sheet, even if this workbook is in use (in a
first task) and even if it is in edit mode, but you don`t get the
latest changes in the workbook which is open in the first task.
if you need the latest data, you could use On Error (not sure if this
works here, if not http://tinyurl.com/58awp8 ) to check if the sheet is
in edit mode and than interact with the user (ie displaying a msgbox to
stop edit mode) or cancel getting the data.

stefan

 

[Jack]

Thank you.
Your reply makes sense.
I do not know, how to open the workbook in a second task.
How to do that?
Let assume the following scenario:
My app on startup opens Excel spreadsheet. That sheet is what the user sees
and it will let him to do editing.
Now, how to open(?) the same sheet in the second task?
Thanks,
Jack

 

[stefan onken]

hi Jack,
CreateObject opens a second task, you can see it in the Windows task
manager.
the task is not visible on the screen/taskbar until you make it
visible.
for using the task it is not necessary to make it visible.

Set moExcelApp = CreateObject("Excel.Application")
'moExcelApp.Visible =True
moExcelApp.Workbooks.Open "filename"
'get data
moExcelApp.activeworkbook.close false
moExcelApp.Quit
Set moExcelApp = Nothing

hth
stefan

 

[Peter T]

I do not understand your approach and how that can help me.

I think I explained the approach adequately so I take it you do not follow
why I suggested to do it like that.

OK, when Excel is in edit mode you cannot attach a reference to the Excel
application. Also if you have previously attached the reference you cannot
do anything with it while in edit mode. AFAIK the only way to exit edit mode
is with Esc or Enter (some other actions may also exit edit mode like arrow
keys, tab etc).

So for your purposes you either need to force exit-edit-mode, or wait until
user does it. The problem relying on the latter is user may have forgotten
to complete some entry and tabbed away to some other window. I don't see a
major problem in sending Esc to Excel, the worse that will happen is user
loses a partial entry, thinks "that's odd" and tries again. In practice the
scenario of you trying to attach to Excel while in edit mode is unlikely to
occur often.

I'm viewing this thread in Excel.programming but I see you have also
multiposted to the vb group. If (?) you are using VB6 adapt what I posted
previously to include OleServerBusyRaiseError (as suggested in the link
referred to by Stefan). Something like this

On error goto errH
App.OleServerBusyRaiseError = True
Set moExcelApp = GetObject(, "Excel.Application")

in the error handler

If Err.Number = -2147418111 And Not bEscpTried Then

hWinActive = GetActiveWindow
bXLnotActive = hWinActive <> hXLwin
If bXLnotActive Then SetForegroundWindow hXLwin
SendKeys "{ESC}"
bEscpTried = True ' to avoid multiple attempts and endless loop
Resume

End If

(variables and declarations as per my previous post)

You might also want to reduce OleServerBusyTimeout from (default in mine)
10000 milliseconds to say 5 sec's I've tested the above in VB6 and it
appears to work fine.

Note OleServerBusyRaiseError is n/a in VBA, unfortunately.


If you want to open a workbook in "your own" instance, and your workbook
might already be open -

Dim oWB As Object ' As Excel.Workbook
Dim bReadOnly As Boolean
Set moExcelApp = CreateObject("excel.application")
' or with early binding
' set moExcelApp = Excel.Application

' moExcelApp.Visible = True '' for testing
moExcelApp.DisplayAlerts = False
Set oWB = moExcelApp.Workbooks.Open("C:\theFile.xls")
bReadOnly = oWB.ReadOnly ' already open in another instance
moExcelApp.DisplayAlerts = True


Regards,
Peter T

 

[Jack]

Thank you both, Peter and Stefan.
I like using the second task approach.
My app can read the cell value without disturbing the user
But, what will happen when my app decides to write back something to the
cell?
Will that info be updated in real time (without disturbing the user I
assume) or it will be updated when the second task will cease to exist or it
will be lost forever?
Jack

 

[Jack]

I had to attend the different things and the 'second task approach' I put on
back burner, but now I am back at it.
I am trying to do use the second task approach but I am unable to access it.
I have started the new thread about it today.
Thanks,
Jack