Can't start Excel more than once with COM

[Simon Morgan]

If you take the sample program ExcelAp1 provided with PB and get it to loop by placing a label at the start of the function (after the DIM statements), and a GOTO label immediately before the END FUNCTION, it works the first time round but not the second: SET oExcelApp = NEW ExcelApplication IN sProgID_Excel returns PB error 99: "A run-time error occurred involving an object".

All the interfaces have been set to NOTHING at the end of the first run through. Something else obviously needs closing, resetting or initialising, but what?

Doing the same thing to the sample Word program, oWord.bas, works fine with Word starting up correctly each time.

Many thanks for any insight.

[Fred Harris]

My guess would be asynchronous function calls within the COM object (Excel). Is it really necessary to shut down Excel? The application itself should be able to maintain itself in a loaded and running state for whatever you need to do with it, such as opening series of workbooks, sheets, etc.

[Cliff Nichols]

Odd.....I get the same error without any modification
aka...it gets as far as

    OBJECT CALL oExcelChart.ChartWizard( _
        Source          = vRange, _
        Gallery         = vGallery, _
        Format          = vFormat, _
        PlotBy          = vPlotBy, _
        CategoryLabels  = vCatLabels, _
        SeriesLabels    = vSerLabels, _
        HasLegend       = vHasLegend, _
        Title           = vTitle)

where

vRange = "A1:G2"
Gallery = -4102
vFormat = 7
vPlotBy = 1
vCatLabels = 1
vSerLabels = 1
vHasLegend = 2
vTitle = "Sales Percentages"

[Simon Morgan]

Thanks for the input.

Fred, the reason I'm trying to open Excel a second time during the same run of my application is to cover the situation where the user has chosen to close Excel (possibly by closing it manually outside the control of my app), but later finds he needs to export to it again.

Cliff, your example seems identical to the sample code supplied with PB/Win, which runs OK for me (the first time!). I am compiling with PB/Win 8.04 and running Excel 2000 under XP Home.

[Cliff Nichols]

Simon, Under debug mode I get the error at the point of the previous post, and it is the supplied example, (I did not change anything)

my test was compiling with PB/Win 8.04 and running Excel 2002 under XP Pro, and running in debug mode in PB

Hope this info helps someone that knows what it may be???

[Fred Harris]

Excel 2000

Fred, the reason I'm trying to open Excel a second time during the same run of my application is to cover the situation where the user has chosen to close Excel (possibly by closing it manually outside the control of my app), but later finds he needs to export to it again.

I see. Sounds plausible. Doesn't that code example you are using check first to see if a running instance of MSExcell.exe can be located, then uses that instead of opening a new instance? If it does why not try eliminating totally the code to close Excel? I'm pretty sure its the code to close Excel and set the references to Nothing that is causing the problem.

From your further elaboration of the problem and your description of your running environment, that is Excel 2000 & Win XP, I do believe I have to admit having severe run-ins with this phenomenon too. My Timber Sale System for my organization I originally wrote with Win 2000 and it used Office 2000 of which Excel is a part. One part of the system opened Excel, read data out of it, created a Word Doc, and populated a database with various data. My program that instantiated and used the services of Excel closed that app out when processing was finished. However, every other time when the user closed my app (which app executed statements to close Excel), the app would GPF upon closing. At that point one could open task manager and find an instance of Excel still in memory. I spent untold hours trying to fix this with no success at all. The issue finally went away when my organization rolled out Win XP with Office XP installed. Same exact software and no more crashes. I never was sure whether the problem was Win 2000 or Office 2000, but now that the approximate same thing is happening to you with Excel 2000 and Win XP, it looks like it is an Excel 2000 problem. What it sounds like to me is that Excell 2000 is messing up its reference count in its AddRef(), Release() internal COM implementation. I don't think you'll be able to fix it. That's why I'm recommending not closing Excel at all. If possible, let the user do it. Just leave it open.

[Simon Morgan]

Cliff, I'm as baffled as you. What happens if you REM out the chart creation code? Presumably the stuff before that which has just populated cells with data will be OK. Perhaps the parameters for creating charts were changed in Excel 2002 (I must admit in Excel 2000 the chart is blank, but at least it appears and doesn't cause an error).

Fred, in my real program I don't actually close Excel myself, but I just wanted to cater for the situation where the user has done. Many thanks for your insight on the problems with this version. I'll test with more recent versions of Excel and, if the problem proves to be specific to Excel 2000, I will ignore it, as you suggest.

The funny thing is, if you end the PB app and restart it everything is OK, so there is something remaining set in memory that prevents Excel opening a second time.

[Michael Mattias]

You sure you're not creating a new instance? Maybe you need ...

OBJECT LET oExcelApp.Visible = vTrue

It won't show if you don't use this.

(code in use not shown)

[Fred Harris]

thoughts

Your post got me to remembering this whole issue Simon, and it finally occurred to me that with my problem I was getting a GPF from Windows, not any kind of PB Error message. And the interesting thing about it was that the message stated that MSExcel.exe had GPF'ed - not my app. I'm pretty sure I have that right - its been a couple years. I'd be in a position to check this issue though because I have multitudes of boxes handy running various versions of Win 2000, Win XP, Office 2000, 2002, XP, etc. I've been too busy with other 'stuff' to fool with it unfortunately.

[Simon Morgan]

Hmmm.. I should have put in more error checking sooner. The ERR 99 that prevents the second run of loading Excel is left over from the charting failing to work properly in the first run (thanks for that idea, Cliff).

Both

OBJECT GET oExcelWorkSheet.Range(vRange) TO vRange

and the subsequent call to oExcelChart.ChartWizard generate ERR 99. That statement above seems to be wrong in using the same variant for both input and output, so I will report it to PB support as it is part of the sample code supplied with PB/Win. Everthing works fine if I replace it with the 2 lines:

OBJECT GET oExcelWorkSheet.Range(vRange) TO oVnt
vRange=oVnt

Michael, I didn't show the code as I am using PB-supplied sample code (which does include OBJECT LET oExcelApp.Visible = vTrue) with the 2 additions mentioned. But thanks for the idea - it has directed me toward bugs elsewhere in my code that caused a second copy of Excel to load (interestingly a copy that didn't paint or update its client area, only its menu and toolbar).

So I think I have it largely sorted know, even with Excel 2000. Many thanks for everyone's help and ideas.

[Simon Morgan]

Prompt to respond as always, PB Support has confirmed that there is an error in the sample code, which will be corrected at the next release.