PIAs for Excel 9.0 Make Code Fail

[James]

I am doing some Excel 2000 automaton using Vb 2005. I am referencing
the Excel 9.0 COM Object Library.

The following code was working fine:

Dim xlApp as new Excel.Application
Dim xlWb as Excel.Workbook
Dim xlSheet as Excel.Worksheet

xlWb = xlApp.Workbooks.Add
xlSheet = xlWb.Worksheets(1)

<some code here >

xlApp.Quit()



The only problem was that Excel.exe would continue in the task manager
after the code was complete.

I followed this link to try to fix the problem:

http://support.microsoft.com/default.aspx?scid=kb;en-us;317109

The above link suggested the following:

Note If you have not already done so, it is recommended that you
download and install the Microsoft Office XP Primary Interop Assemblies
(PIAs).
http://support.microsoft.com/kb/328912/


I downloaded and registered the PIAs. Now my old code will not run.

xlWb = xlApp.Workbooks.Add now gives the following error:

Unable to cast COM object of type
'Microsoft.Office.Interop.Excel.ApplicationClass'
to interface type 'Excel.Application'. This operation failed because
the QueryInterface
call on the COM component for the interface with IID
'{000208D5-0000-0000-C000-000000000046}'
failed due to the following error: Library not registered.
(Exception from HRESULT: 0x8002801D (TYPE_E_LIBNOTREGISTERED)).


Could it be that the PIAs I downloaded replaced the Excel 9.0 Object
Library with the 10.0 object library? Any Idea how to get back to
where I was? Do I need to re-install Excel 2000? Re-install VS 2005?


Thanks very much,

James

 

[RobinS]

The answer to the first problem is that in your
<some code here> you have something that is creating an
object in the background in Excel, and you are not
disposing of it, so even though you do xlApp.Quit,
it is not closing Excel. (This is the voice of experience.)

To fix that, try commenting out all the code, and then uncommenting
pieces at a time and trying it out. (I ended up uncommenting
one line at a time, and it was a royal pain, but it worked.)
Be sure to get rid of Excel in the <Processes> in the
Task manager each time.

If you created any of the <some code here> code using the
macro recorder, you have to be careful. Some of it needs to be
restructured, or you have this problem. IIRC, I had a problem
with SelectedRange or something like that -- it created a
Range object in Excel, and then wouldn't dispose of it.

As for the second problem, can you deinstall the PIAs? Does
it show up in the Add/Remove programs in the control panel?
The only other thing I can think of is to reinstall Excel.

Good luck.
Robin S.

 

[James]

Thanks for the response.

I wanted to let anyone else with this problem know the solution to the
problem with the PIAs:

If you are developing for Office 2000, DO NOT INSTALL the MS PIAs.
They will cause any references to Office 2000 to default to Office XP
in which case if you only have Office 2000 installed on your machine,
cause your solution to fail!!! Microsoft should warn us of this but
they don't.

The only way I was able to get my app working again was to activate a
system restore point at a point prior to the installation of the PIAs.