Trap this Error

[Amod]

hi

all

I am coding in Vb6.0 with the following code.I am getting
an error "Error 462: The remote server machine does not
exist or is unavailable." while automating Excel 2000
through Vb 6.0. The code is given below ....
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dim xlApp As Excel.Application
Dim xlWorkBook1 As New Excel.Workbook
Dim WithEvents eveXlWorkbook1 As Excel.Workbook
-----------------------------------------
Private Sub Command1_Click()
Set xlApp = CreateObject("Excel.application")
Set xlWorkBook1 = xlApp.Workbooks.Open("c:\book2.xls")
xlApp.EnableEvents = True

Set eveXlWorkbook1 = xlWorkBook1
xlApp.Visible = True
End Sub
------------------------------------------
Private Sub eveXlWorkbook1_BeforeClose(Cancel As Boolean)
xlApp.EnableEvents = False
Set xlWorkBook1 = Nothing
xlApp.Quit
Set xlApp = Nothing
End Sub
----------------------------------------------
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

this is a standard exe project with a form conatains one
command button. When I click on the command button for the
first time it works nicely, when I click on the command
button second time I get the above error.

this error comes on the line "Set eveXlWorkbook1 =
xlWorkBook1" in "Command1_Click()" procedure. this error
comes because of "Unqualified Code for office object"

check this url
"http://support.microsoft.com/default.aspx?scid=kb;en-
us;319832"

Please suggest any changes to code so it will work.
I have tried with "Set eveXlWorkbook1 = xlapp.workbooks
(1)" and "Set eveXlWorkbook1 = xlapp.ActiveWorkbook"

waiting for reply
regards
Amod

 

[Jan Karel Pieterse]

Hi,

You are using a Workbook Before_Close Event in which you
close Excel and set it's object variable to nothing. But
as I see it, this Before_Close code will run BEFORE the
workbook is closed and hence you're quitting Excel before
it has closed the workbook. Maybe that causes a mixup
somehow? Try changing your code so that the Before_close
routine can finish without quitting Excel and setting the
Object variables to nothing.

In Excel VBA, I'd use an OnTime EVent that sets a little
sub to run to do that (this ensures the event sub is first
ended, the event is processed and then the scheduled code
finishes off)

Regards,

Jan Karel Pieterse
Excel TA/MVP

 

[Rob Bovey]

In addition to Jan Karel's advice, I'd suggest changing the following
variable declaration:

Dim xlWorkBook1 As New Excel.Workbook

to:

Dim xlWorkBook1 As Excel.Workbook

The Excel.Workbook object is not creatable, so even though VB allows you to
use the As New syntax in its variable declaration it's not meaningful to do
so and can lead to errors (although I don't think it's related to the
problem you're experiencing).

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *