Is Excel busy? 1

[HughLerwill]

Dear Skip and All,

There is a visible instance of Excel running and the user has edited a cell but not confirmed the edit (left the cell) yet.
I try to use Excel from VB6 but the OS (I guess) comes back with a Component busy message. How can the VB6 app complete the edit in Excel or inform me of the condition so that the rather confusing OS error message is avoided.

regards Hugh

 

[SkipVought]

Hi,

You might need to interrogate in a loop WithEvents until Excel show NOT busy.

Skip,

_{[red]Be Advised![/red]
Alcohol and Calculus do not mix!
If you DRINK, don't DERIVE!}

 

[HughLerwill]

Skip,

Thanks for coming back again.

My Excel object is based on an Object object rather than an Excel.Application object i.e.;

Dim objXl as Object
Set ObjXl = Create/ GetObject...Excel.Application..etc

and so late bound, I don't think I can use WithEvents in that case.

Ref your <<until Excel show NOT busy.>>

I would rather attend to completion of the edit in VB6 code or plainly inform the user that he has an edit which must be completed manually before things can continue. Pre-empting the OS error message and replacing it with a less cryptic one would be fine.

regards Hugh

 

[SkipVought]

Where is your process running? How does it interact with the Excel user?

Skip,

_{[red]Be Advised![/red]
Alcohol and Calculus do not mix!
If you DRINK, don't DERIVE!}

 

[HughLerwill]

Skip,


VB6 we do;

Sub ..

Declare all vars Explicit including dim xlobj as Object

Gosub OpenExcel

With xlObj
.Visible = True
If .WindowState = xlMinimized Then .WindowState = xlNormal
End With

set xlObj = Nothing 'Excel continues running

exit sub


OpenExcel:

Screen.MousePointer = vbHourglass: DoEvents
On Error Resume Next
Set xlObj = GetObject(, "Excel.Application") 'if an instance is already running and one of it's
' workbooks/worksheets/cells are being edited the OS message is displayed
' when trying to run this line

If Err Then Set xlObj = CreateObject("Excel.Application"): newinst = True
On Error GoTo 0
With xlObj
'Redisplay the existing file if it is aleady displayed in Excel
If Not newinst Then
For Each wb In .Workbooks()
If UCase$(wb.Name) = UCase$(Mid$(ImportFileName$, InStrRev(ImportFileName$, "\") + 1)) Then
wb.Activate: found = True: Exit For
End If
Next
End If

If newinst Or Not found Then .Workbooks.Open ImportFileName$ 'not already loaded so get it
.Sheets(1).Select
End With
Screen.MousePointer = vbDefault
Return

End Sub

OS message reads;

"An action cannot be completed because a component (Microsoft Excel - FileName.xls) is busy. Choose switch to activate the component and correct the problem."
Option buttons are Switch-to, Retry, and Cancel.

regards Hugh

 

[SkipVought]

On what statement does this occur?

Skip,

_{[red]Be Advised![/red]
Alcohol and Calculus do not mix!
If you DRINK, don't DERIVE!}

 

[HughLerwill]

Skip,

on

Set xlObj = GetObject(, "Excel.Application")

as remmed

regards Hugh

 

[SkipVought]

Look at the VB Help examples on GetObject. There is sample code to registering Excel as running and test for the existance of Excel running.

Skip,

_{[red]Be Advised![/red]
Alcohol and Calculus do not mix!
If you DRINK, don't DERIVE!}

 

[HughLerwill]

Skip,

<<Look at the VB Help >>

Hmmm I just did and hmmm. It's dinner time, I'm tired. I'll look at it again in the morning.

Maybe doing a SendKeys vbcr to the Excel window would be enough...

Thanks for your help.

Kind regards Hugh,

 

[HughLerwill]

Skip,

Fed and a little derived.

I expect you may have thought that;

Set xlObj = GetObject(ImportFileName$ & "!Application")

may get the original instance back in its semi edited state but unfortunately it just fires up a new instance of the Read only variety (whether the first in in semi-edited state or not).

I guess I need an xlObj(Module level) which persists as long as the file is displayed in Excel, then all I need is .visible = true to display it, failing that the Application.Ready property could help.

Thanks for nudging me along....Using GetObject like that had'nt occured to me lately.

regards Hugh

 

[HughLerwill]

Skip,

I'd skipped the Sample code but see it now, but I wonder on the significance of the;

SendMessage hWnd, WM_USER + 18, 0, 0

with regard to the rest of the code which appears to make no use of it?

Hugh

 

[Droops]

Hi to all,

not sure this can help in your case, but as you mentioned you were ready to change the message, here's a link that may be interesting for you.

http://msdn.microsoft.com/library/d...en-us/vb98/html/vbprooleserverbusytimeout.asp

I have never needed to modify this App property but hope it helps.

Droops