Erwin Craps
Erwin.Craps at ithelps.be
Fri Oct 10 17:52:20 CDT 2003
Dim objExcel as object
Dim objWorksheet as object
This is more clear than variant.
-----Oorspronkelijk bericht-----
Van: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] Namens Jim DeMarco
Verzonden: vrijdag 10 oktober 2003 21:09
Aan: Access Developers discussion and problem solving
Onderwerp: RE: [AccessD] Excel automation code fails
>>You can use object type to<<
Sorry don't understand what you mean.
BTW - it works on her machine now if she uses a Variant for the VBIDE
objects.
Thanks,
Jim DeMarco
Director of Product Development
HealthSource/Hudson Health Plan
-----Original Message-----
From: Erwin Craps [mailto:Erwin.Craps at ithelps.be]
Sent: Friday, October 10, 2003 3:02 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Excel automation code fails
You can use object type to.
This will result in not having the autosuggest while developing.
I noticed that this solved some issues to. Al hase to do with late/early
binding.
-----Oorspronkelijk bericht-----
Van: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] Namens Jim DeMarco
Verzonden: vrijdag 10 oktober 2003 17:38
Aan: Access Developers discussion and problem solving
Onderwerp: RE: [AccessD] Excel automation code fails
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".
************************************************************************
***********
_______________________________________________
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