Excel Process won't Quit

[k2a]

Hi to all,
I create an Excel process and fill an array, than close the Excel process. This works fine with small Excel tables having about 850 rows.
However, using Excel tables with up to 1500 rows it fails, that means the Excel process does not quit.

The method involved keeps in a wait state because the EXCEL.EXE process is still alive.

The creation of the Excel process goes like this:
loExcel = CREATEOBJECT("excel.application")
loExcel.Workbooks.Open(gcSourceFile)

And to closing the process like this:
loExcel.Workbooks.Close()
loExcel.Quit()

Has anyone an ideas how to fix that?
Any suggestions are appreciated.

Regards, Klaus

 

[Mike Lewis]

Are you writing to the workbook?

If so, then Workbooks.Close() might be prompting the user to save the changes. And if Excel is invisible at the point, you won't be aware of that, and it will stay sitting on the prompt indefinitely.

If that's the case, you should either pass .F. to Close(), to tell it to discard the changes, or pass .T. to have it save them (and also pass a filename if you have not yet given a name to the workbook).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[k2a]

Hi Mike,
Thank you for your quick response.

In fact i'm only reading from the workbook and passing .F. to Close() does not solve it. What confuses me is the fact that small Excel table do not fail. Also when splitting the Excel table in an upper and lower part the then is works.

What do you mean by >> (and also pass a filename if you have not yet given a name to the workbook).

Regards, Klaus

 

[Olaf Doschke]

It's like Mike says, the close without a paramter will prompt the user with a question. This also appears without Excel being visible to me:

o = CREATEOBJECT("excel.application")
o.Workbooks.Add()
o.ActiveWorkbook.ActiveSheet.Cells(1,1).Value="Hello, Cell"
o.Workbooks.Close() && dialog appears
o.Quit()

Close has no parameters, though, for WorkBooks, only for ActiveWorkBook, which would also be sufficient, as you typically only automate one workbook at a time, so to disregard changes you could do:

o = CREATEOBJECT("excel.application")
o.Workbooks.Add()
o.ActiveWorkbook.ActiveSheet.Cells(1,1).Value="Hello, Cell"
o.ActiveWorkbook.Close(.F.)
o.Quit()

You can alos set loExcel.DisplayAlerts = .F., but it will also rather cancel changes and not save. Once you Save or SaveAs your excel file no alerts should occur anyway, so this (hidden) dialog probably hindering the code to continue to the Quit is pointing to a problem in the rest of your code, creating a sheet and not saving it.

The other solution is to make Excel visible after you're done with it and let the user decide, if to work on the file or to save it or to discard it, to keep excel or to quit it, just do loExcel.Visible = .T. for that.

Bye, Olaf.



Bye, Olaf.

 

[k2a]

Hi Mike, hi Olaf,

When i changed the statement to loExcel.Workbooks.Close(.F.) an error pops up: OLE error Unknown Name
I'm only reading from the Excel table.

Regards, Klaus

 

[Olaf Doschke]

Well, I already said there is no parameter of Workbooks.Close(), Mike confused that with ActiveSheet.Close()
Both Workbooks and Sheet have a close method, but at least in Excel2010 Workbooks.Close() has no parameter, therefor the error unknown name.

If you only read from the workbooks it's even more susupicious Close() - even without parameters - stops VFP from continuing. It does only, if you make changes to a sheet.

Try without the Close, just loExcel.Quit().

Also, for debugging purposes, set loExcel.Visible = .T. and see what shows up, eg what dialog appears.

Bye, Olaf.

 

[k2a]

Hi Olaf,

Just made the changes you recommended. Now the error disappeared and the method does not hang in a wait loop anymore.

However, the EXCEL.EXE process is still alive, using a smaller Excel table instead the EXCEL.EXE process will disappears.

That is somewhat mysterious.

Regards, Klaus

 

[Mike Lewis]

I already said there is no parameter of Workbooks.Close(), Mike confused that with ActiveSheet.Close()

Hmmm. I don't think so.

I haven't actually tried this today, but according to the VBA Help:

Workbook objects use Syntax 2

and Syntax 2 is described as:

expression.Close(SaveChanges, FileName, RouteWorkbook)

Ah, wait. I originally referred to Workbooks.Close. I should have said Workbook.Close (without the "s"). So, it looks like you're right, Olaf.

Klaus, have you tried doing this:

 loExcel.Workbook.Close(.F.)

rather than:

loExcel.Workbook[b]s[/b].Close(.F.)

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

Mike, you are almost at it, but there is no object calls loExcel.Workbook, there is loExcel.ActiveWorkbook or loExcel.Workbooks(1 (or 2,3,4. etc)
There is no need to close all or one workbook before quitting Excel, if you only read from the sheets, though.

Klaus, yes the Excel neither erroring nor vanishing from the process list is a mystery. It's hard to see what in an excel sheet may cause this. Do you have any Macros that run at start and thereby cause a change to the excel sheet excel wants to save?

The easy way out I use in almost any case is to not close excel at all. That's more in conjunction with what users expect, if you do it after creating a sheet, than after reading it. What do you see from Excel, if you make it visible before Quitting it?

Bye, Olaf.

 

[k2a]

Hi Mike,

When placing loExcel.Workbook.Close(.F.) to my code, then an error message appears: Member Workbook does not evaluate to an object.

Regards, Klaus

 

[Mike Lewis]

My mistake. There is a Workbook object, and it does have a Close method. But it's not a member of the Excel application object.

Referring to Klaus's original code, you need something like this:

loExcel = CREATEOBJECT("excel.application")
[b]loBook =[/b] loExcel.Workbooks.Open(gcSourceFile)

 * do stuff

[b]loBook.[/b]Close([b].F.[/b])
loExcel.Quit()

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

Mike, yes, that is good, as loBook is the workbook you loaded, but there also is loExcel.ActiveWorkbook and the excel file you load automatically gets the ActiveWorkbook. The advantage of using your approach is, this stays valid, even if the active workbook would change somehow.

Bye, Olaf.

 

[k2a]

Mike, my origional looks as this:
loExcel = CREATEOBJECT("excel.application")
loExcel.Workbooks.Open(gcSourceFile)
lnUsedRows = loBook.ActiveWorkbook.ActiveSheet.UsedRange.Rows.Count && Number of rows
lnUsedCol = loBook.ActiveWorkbook.ActiveSheet.UsedRange.Columns.Count && Number of columns
DIMENSION gaAPOBank(lnExRow, lnArrayCol)

Now, using your code of: loBook = loExcel.Workbooks.Open(gcSourceFile). How to get the number of rows and columns?
This does not work: lnUsedRows = loBook.ActiveWorkbook.ActiveSheet.UsedRange.Rows.Count

Regards, Klaus

 

[Olaf Doschke]

loBook is the same as loExcel.ActiveWorkbook, so use loBook.ActiveSheet.UsedRange.Rows.Count

Bye, Olaf.

 

[k2a]

Mike, congratulation you code fixed the problem, now it also terminate the EXCEL.EXE process.
Olaf, thank you for helping with the new code: loBook.ActiveSheet.UsedRange.Rows.Count

Thanks so much for your outstanding support.

Regards, Klaus

 

[Mike Lewis]

Glad to hear it's working, Klaus.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[k2a]

Hi Mike,
There is another strange behavior with my global array which disappears alternatingly. Your code has nothing to do with the disappearance of the array because it also happened before.

I am not sure if a new thread should be open for it??

There seems to be an alternating consistency between failure and success. I tested that with a small Excel table. The Excel data are used to fill a global array called gaAPObank.
My code at the end looks now like this:
loBook.Close(.F.)
loExcel.Quit()
thisform.cmdSearch.Enabled = .T. (here sits a breakpoint)

Now I can check that my array got filled with the same amount of data as Excel table has. With the cmdSearch button a new method is started to scan the array for certain pattern.
The first command line in that new method is:
lnBankRows = ALEN(gaAPOBank,1)

Alternatingly a message pops up: Variable gaAPObank is not found. So that means between my break and the click on cmdSearch button, which is on the same form, the array vanished.

What could be the reason that a public array disappears?

Currently I catch the failure and start over again, but it is somewhat annoying when using large Excel tables, which takes a few minutes to fill the array.

Regards, Klaus

 

[Dan Freeman]

This probably calls for a new thread, but what you describe indicates you need to understand variable scoping in Foxpro.

There is no such thing as a "global array". You may have one of three possible scopes: Public, Private, and Local. Private is the default lacking any other declaration. A private array goes out of scope at the end of the method that declares it and therefore disappears.

To make an array public (which means it is visible everywhere), you use the PUBLIC keyword. Are you doing that?

(Having said that, a public array is almost never the best thing to use. )

 

[k2a]

Dan, to create a global array in VFP you have to use the PUBLIC keyword, I know that. But generally speaking it becomes a global array.

The reason to delcare the array as public is because it become quite large and take a few minutes to create it. When you close the form and open it later on, the array is still alive and you may skip the creation process. Personally, I try to eliminate most my public varables. Following this attitude I try to place the array into a new from property and see how it behaves then.

Thank you for your reply.

Regards, Klaus

 

[Dan Freeman]

My point is that there is nothing in Foxpro called a "global array". It's a term you've decided to use that has no meaning in the language. You can cause real confusion by using terms that don't exist (global array) to describe things that do exist (public array).

Persisting an array across multiple instances of a single form is one legitimate use for a public array, although I'd probably make it a property of the application object instead of making it a public variable. That's just my personal bias.

Be that as it may, you say the array is "going away". The only way a public array can "go away" is if you either release it, or it isn't public in the first place.