[AccessD] Excel automation code fails

Jim DeMarco Jdemarco at hshhp.org
Fri Oct 10 10:37:39 CDT 2003


Good point.  I asked the developer to see if she was properly cleaning up.  Meanwhile, I got the code to run on my machine (it was giving me a Type Mismatch error) by changing the data type of both VBIDE objects to Variant.

Thanks,

Jim DeMarco


-----Original Message-----
From: Erwin Craps [mailto:Erwin.Craps at ithelps.be]
Sent: Friday, October 10, 2003 2:39 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Excel automation code fails


Yes I can....

Has some serious troubles resolving this issue.
I had same problem with some objets in excel not all and only the second
time I ran the code.

The first time it works, the second time I got object errors.
When you close and reopen access it works again for 1 time.

It has to do with early binding and not closing object properly.

I see your code already uses late binding but you should verify if you
properly quit excel after setting all object to nothing..

Particaly when developping you sometime stop the code to run and by that
not closing the objects properly. Closing access an reopen is your only
option at that moment.

Often forgotten is when you leave the function after an error has ocured
not to close objects. Objects need to be closed to when leaving a
function after an error.


 

Erwin Craps

Zaakvoerder 

www.ithelps.be/jonathan

 

This E-mail is confidential, may be legally privileged, and is for the
intended recipient only. Access, disclosure, copying, distribution, or
reliance on any of it by anyone else is prohibited and may be a criminal
offence. Please delete if obtained in error and E-mail confirmation to
the sender.

IT Helps - I.T. Help Center  ***  Box Office Belgium & Luxembourg

www.ithelps.be  *  www.boxoffice.be  *  www.stadleuven.be

IT Helps bvba* ** Mercatorpad 3 **  3000 Leuven

IT Helps  *  Phone: +32 16 296 404  *  Fax: +32 16 296 405 E-mail:
Info at ithelps.be 

Box Office **  Fax: +32 16 296 406 **  Box Office E-mail:
Staff at boxoffice.be



-----Oorspronkelijk bericht-----
Van: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] Namens Jim DeMarco
Verzonden: vrijdag 10 oktober 2003 4:36
Aan: Access Developers discussion and problem solving
Onderwerp: RE: [AccessD] Excel automation code fails


John,

It works in my debug window too but not in a form code module.  I don't
know why there's a difference.

Thanks,

Jim DeMarco

-----Original Message-----
From: John Sullivan [mailto:jonsulli at swbell.net]
Sent: Thursday, October 09, 2003 4:05 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Excel automation code fails


Jim,

Given the path and name of an Excel sheet on my pc, this seems to work 
ok and gives the following in the debug window:

 2
ThisWorkbook
 2
Sheet1
 2

If you have more than A97 installed on your test machine, check that the

references are actually set and all match the version of office your 
running.

HTH,

John Sullivan


Jim DeMarco wrote:

>Can anyone tell me why this code returns an "Object does not support 
>automation" error when run from a form code module, but works when 
>pasted into the debug window?  App (A97) references the VBA 
>Extensibility and Excel libraries.
>
><code>
>Dim objXLapp As Excel.Application
>Dim objXLWorkbooks As Excel.Workbooks
>Dim objXLABC As Excel.Workbook
>
>Dim objProject As VBIDE.VBProject
>Dim objComponent As VBIDE.VBComponent
>
>
>Set objXLapp = New Excel.Application
>Set objXLWorkbooks = objXLapp.Workbooks
>Set objXLABC = objXLWorkbooks.Open("c:\work\pcpfile.xls")
>Set objProject = objXLABC.VBProject
>
>Debug.Print objProject.VBComponents.Count 'code fails here from form 
>module
>
>
>For Each objComponent In objProject.VBComponents
>   Debug.Print objComponent.Name
>   Debug.Print objComponent.CodeModule.CountOfLines
>Next
></code>
>
>TIA,
>
>Jim DeMarco
>Director of Product Development
>HealthSource/Hudson Health Plan
>
>
>***********************************************************************
>************
>"This electronic message is intended to be for the use only of the
named recipient, and may contain information from Hudson Health Plan
(HHP) that is confidential or privileged.  If you are not the intended
recipient, you are hereby notified that any disclosure, copying,
distribution or use of the contents of this message is strictly
prohibited.  If you have received this message in error or are not the
named recipient, please notify us immediately, either by contacting the
sender at the electronic mail address noted above or calling HHP at
(914) 631-1611. If you are not the intended recipient, please do not
forward this email to anyone, and delete and destroy all copies of this
message.  Thank You".
>***********************************************************************
************
>
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com 
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>
>  
>

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


************************************************************************
***********
"This electronic message is intended to be for the use only of the named
recipient, and may contain information from Hudson Health Plan (HHP)
that is confidential or privileged.  If you are not the intended
recipient, you are hereby notified that any disclosure, copying,
distribution or use of the contents of this message is strictly
prohibited.  If you have received this message in error or are not the
named recipient, please notify us immediately, either by contacting the
sender at the electronic mail address noted above or calling HHP at
(914) 631-1611. If you are not the intended recipient, please do not
forward this email to anyone, and delete and destroy all copies of this
message.  Thank You".
************************************************************************
***********

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


***********************************************************************************
"This electronic message is intended to be for the use only of the named recipient, and may contain information from Hudson Health Plan (HHP) that is confidential or privileged.  If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or use of the contents of this message is strictly prohibited.  If you have received this message in error or are not the named recipient, please notify us immediately, either by contacting the sender at the electronic mail address noted above or calling HHP at (914) 631-1611. If you are not the intended recipient, please do not forward this email to anyone, and delete and destroy all copies of this message.  Thank You".
***********************************************************************************



More information about the AccessD mailing list