I posted a request for advice on the programming newsgroup but nobody seems
familiar with my new problem. Having done further testing and produced some
data-neutral code to demonstrate the problem, I am posting it here.

The problem was first noted on my office PC running an application with
Runtime. The machine is about 4 years old and the OS is 98SE - haven't
checked the specs otherwise. I have reproduced the problem on my laptop,
in the development environment. This is a Toshiba Satellite 1800 with 120MB
RAM, also running 98SE.

The application writes data to Excel. Normally, it works fine. The routine
can be repeated again and again without problems. It doesn't seem to matter
if a number of instances are open and they close properly as required.

The difficulty arises with extra large Excel files. In the case of my
application it happens to be the maximum data report which runs to 266 rows
by 56 columns.

The Excel file gets written OK, but something goes wrong when it is closed.

For the purpose of this explanation, assume no other instance of Excel has
been opened.

The following effects are noted:

If you press Control+Alt+Delete to view running processes, Excel is still
there.

If you try to rerun the routine, it won't work. It can't handle the command
'form.oExcel = new OleAutoClient("Excel.Application")'. You might get an
error message if you are lucky, or it hangs. With 'Try/catch' the
developer's error message takes a long time to appear, if it doesn't hang.

If you try to open the development environment, it doesn't load properly.
You get the navigator box, but no file list, and it hangs.

Providing the computer hasn't by this time become so unstable it needs a
reboot, things can be put back in working order by Ending the (Excel) Task
in the running processes list.

This problem does not arise with my home PC, an Advent 2000 running XP. The
application (Runtime driven) will write the full report as many times as I
like. Also, the test code I am submitting works fine in that environment.

Here is the test code - it produces the same fault as does my application on
my laptop.

It is a non-MDI form. It creates an array of column references and uses
that in writing data to Excel. The sheet is 104 columns by 302 and the
columns are totalled at the bottom.

I hope this is sufficient information, and look forward to feedback.

Mike Nunn

//WriteExcel.wfm - written by Mike Nunn
//to test problem in writing large files to Excel where the operating system
is SE98

** END HEADER -- do not remove this line
//
// Generated on 15/01/05
//
parameter bModal
local f
f = new WriteExcelForm()
if (bModal)
f.mdi = false // ensure not MDI
f.readModal()
else
f.open()
endif

class WriteExcelForm of FORM
with (this)
onOpen = class::FORM_ONOPEN
onClose = class::FORM_ONCLOSE
height = 5.6818
left = 51.0
top = 7.9091
width = 40.0
text = ""
mdi = false
endwith

this.TEXT1 = new TEXT(this)
with (this.TEXT1)
height = 1.0
left = 9.0
top = 1.0
width = 22.0
text = "Writing 300 rows to Excel"
endwith

this.TEXT2 = new TEXT(this)
with (this.TEXT2)
height = 1.0
left = 2.0
top = 2.5
width = 36.0
colorNormal = "silver/navy"
alignHorizontal = 1 // Center
text = "Space for warning message."
borderStyle = 2 // Lowered
endwith

this.TEXTLABEL1 = new TEXTLABEL(this)
with (this.TEXTLABEL1)
height = 1.0
left = 18.0
top = 4.0
width = 12.0
text = "Row counter"
endwith

this.ENTRYFIELD1 = new ENTRYFIELD(this)
with (this.ENTRYFIELD1)
enabled = false
height = 1.0
left = 31.0
top = 4.0
width = 7.0
value = 0
endwith

function form_onOpen
public GoToIt
form.arraybuild()
return

function arraybuild
form.ColumnArray = new array(300,1)
mASCNo = 65
X = 1
Do while mASCNo <> 91
form.ColumnArray[X,1] = LTrim(CHR(mASCNo))
X = X + 1
mASCNo = mASCNo + 1
Enddo
Y = 65
Do while .T.
mLetter = LTrim(CHR(Y))
mASCNo = 65
Do while mASCNo <> 91
form.ColumnArray[X,1] = mLetter + LTrim(CHR(mASCNo))
If form.ColumnArray[X,1] = 'CZ' //Max 'IV'
Exit
Endif
X = X + 1
mASCNo = mASCNo + 1
Enddo
If form.ColumnArray[X,1] = 'CZ' //Max 'IV'
Exit
Endif
Y = Y + 1
Enddo
form.ExcelWrite()
return

function ExcelWrite
GoToIt = .T.
Try
form.oExcel = new OleAutoClient("Excel.Application")
Catch (Exception e)
//If this programme is run a second time, this will fire
//Or the system may hang here.
GoToIt = .F.
form.text2.colornormal = 'red/silver'
form.text2.text = 'Excel problem - cannot proceed'
Endtry
If GoToIt = .T.
form.oExcel.Workbooks.add()
form.oExcel.ActiveWindow.Zoom = 75
form.oExcel.Sheets("Sheet1").Select()
mRow = 1
Do while mRow <> 301
form.entryfield1.value = mRow
X = 1
Do while X <> 104 //Max 256
mCol = form.ColumnArray[X,1]
mCell = mCol + Ltrim(Str(mRow))
form.oExcel.Range(mCell).Select()
form.oExcel.ActiveCell.FormulaR1C1 = mRow * 3
X = X + 1
Enddo
mRow = mRow + 1
Enddo
mTot = 0
mRow = 302
form.entryfield1.value = mRow
form.text2.text = 'Totalling - please wait'
X = 1
Do while X <> 104 //Max 256
mStartCell = form.ColumnArray[X,1] + LTrim(Str(1))
mCell = form.ColumnArray[X,1] + LTrim(Str(mRow))
mEndCell = form.ColumnArray[X,1]+ LTrim(Str(mRow-2))
mCellRange = mStartCell + ':' + mEndCell
mThisCell = form.oExcel.ActiveSheet.cells(mRow,X)
with (mThisCell)
formula = "=Sum(" + mCellRange + ")"
endwith
X = X + 1
Enddo
mCellRange = "A1:CZ302" //Max IV302
form.oExcel.Range(mCellRange).Select()
form.oExcel.Selection.NumberFormat = ;
[_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)]
form.oExcel.Columns("A:CZ").Autofit()
form.oExcel.Range("A1").Select()
form.oExcel.visible = .T.
form.oExcel.quit()
form.close()
Endif
return

function form_onClose
If GoToIt = .T.
release object form.oExcel
Endif
return
endclass

From:	Michael Nunn
Subject:	Re: Excel closing problem
Date:	Sat, 15 Jan 2005 18:25:21 -0000

Sorry -forgot to say I am using dBasePlus v 2.21.

"Michael Nunn" wrote in message
news:ZZjPZ9y%23EHA.1132@news-server...
>I posted a request for advice on the programming newsgroup but nobody seems
>familiar with my new problem. Having done further testing and produced
>some data-neutral code to demonstrate the problem, I am posting it here.
>
> The problem was first noted on my office PC running an application with
> Runtime. The machine is about 4 years old and the OS is 98SE - haven't
> checked the specs otherwise. I have reproduced the problem on my laptop,
> in the development environment. This is a Toshiba Satellite 1800 with
> 120MB RAM, also running 98SE.
>
> The application writes data to Excel. Normally, it works fine. The
> routine can be repeated again and again without problems. It doesn't seem
> to matter if a number of instances are open and they close properly as
> required.
>
> The difficulty arises with extra large Excel files. In the case of my
> application it happens to be the maximum data report which runs to 266
> rows by 56 columns.
>
> The Excel file gets written OK, but something goes wrong when it is
> closed.
>
> For the purpose of this explanation, assume no other instance of Excel has
> been opened.
>
> The following effects are noted:
>
> If you press Control+Alt+Delete to view running processes, Excel is still
> there.
>
> If you try to rerun the routine, it won't work. It can't handle the
> command 'form.oExcel = new OleAutoClient("Excel.Application")'. You might
> get an error message if you are lucky, or it hangs. With 'Try/catch' the
> developer's error message takes a long time to appear, if it doesn't hang.
>
> If you try to open the development environment, it doesn't load properly.
> You get the navigator box, but no file list, and it hangs.
>
> Providing the computer hasn't by this time become so unstable it needs a
> reboot, things can be put back in working order by Ending the (Excel) Task
> in the running processes list.
>
> This problem does not arise with my home PC, an Advent 2000 running XP.
> The application (Runtime driven) will write the full report as many times
> as I like. Also, the test code I am submitting works fine in that
> environment.
>
> Here is the test code - it produces the same fault as does my application
> on my laptop.
>
> It is a non-MDI form. It creates an array of column references and uses
> that in writing data to Excel. The sheet is 104 columns by 302 and the
> columns are totalled at the bottom.
>
> I hope this is sufficient information, and look forward to feedback.
>
> Mike Nunn
>
>
> //WriteExcel.wfm - written by Mike Nunn
> //to test problem in writing large files to Excel where the operating
> system is SE98
>
> ** END HEADER -- do not remove this line
> //
> // Generated on 15/01/05
> //
> parameter bModal
> local f
> f = new WriteExcelForm()
> if (bModal)
> f.mdi = false // ensure not MDI
> f.readModal()
> else
> f.open()
> endif
>
> class WriteExcelForm of FORM
> with (this)
> onOpen = class::FORM_ONOPEN
> onClose = class::FORM_ONCLOSE
> height = 5.6818
> left = 51.0
> top = 7.9091
> width = 40.0
> text = ""
> mdi = false
> endwith
>
> this.TEXT1 = new TEXT(this)
> with (this.TEXT1)
> height = 1.0
> left = 9.0
> top = 1.0
> width = 22.0
> text = "Writing 300 rows to Excel"
> endwith
>
> this.TEXT2 = new TEXT(this)
> with (this.TEXT2)
> height = 1.0
> left = 2.0
> top = 2.5
> width = 36.0
> colorNormal = "silver/navy"
> alignHorizontal = 1 // Center
> text = "Space for warning message."
> borderStyle = 2 // Lowered
> endwith
>
> this.TEXTLABEL1 = new TEXTLABEL(this)
> with (this.TEXTLABEL1)
> height = 1.0
> left = 18.0
> top = 4.0
> width = 12.0
> text = "Row counter"
> endwith
>
> this.ENTRYFIELD1 = new ENTRYFIELD(this)
> with (this.ENTRYFIELD1)
> enabled = false
> height = 1.0
> left = 31.0
> top = 4.0
> width = 7.0
> value = 0
> endwith
>
>
> function form_onOpen
> public GoToIt
> form.arraybuild()
> return
>
> function arraybuild
> form.ColumnArray = new array(300,1)
> mASCNo = 65
> X = 1
> Do while mASCNo <> 91
> form.ColumnArray[X,1] = LTrim(CHR(mASCNo))
> X = X + 1
> mASCNo = mASCNo + 1
> Enddo
> Y = 65
> Do while .T.
> mLetter = LTrim(CHR(Y))
> mASCNo = 65
> Do while mASCNo <> 91
> form.ColumnArray[X,1] = mLetter + LTrim(CHR(mASCNo))
> If form.ColumnArray[X,1] = 'CZ' //Max 'IV'
> Exit
> Endif
> X = X + 1
> mASCNo = mASCNo + 1
> Enddo
> If form.ColumnArray[X,1] = 'CZ' //Max 'IV'
> Exit
> Endif
> Y = Y + 1
> Enddo
> form.ExcelWrite()
> return
>
> function ExcelWrite
> GoToIt = .T.
> Try
> form.oExcel = new OleAutoClient("Excel.Application")
> Catch (Exception e)
> //If this programme is run a second time, this will fire
> //Or the system may hang here.
> GoToIt = .F.
> form.text2.colornormal = 'red/silver'
> form.text2.text = 'Excel problem - cannot proceed'
> Endtry
> If GoToIt = .T.
> form.oExcel.Workbooks.add()
> form.oExcel.ActiveWindow.Zoom = 75
> form.oExcel.Sheets("Sheet1").Select()
> mRow = 1
> Do while mRow <> 301
> form.entryfield1.value = mRow
> X = 1
> Do while X <> 104 //Max 256
> mCol = form.ColumnArray[X,1]
> mCell = mCol + Ltrim(Str(mRow))
> form.oExcel.Range(mCell).Select()
> form.oExcel.ActiveCell.FormulaR1C1 = mRow * 3
> X = X + 1
> Enddo
> mRow = mRow + 1
> Enddo
> mTot = 0
> mRow = 302
> form.entryfield1.value = mRow
> form.text2.text = 'Totalling - please wait'
> X = 1
> Do while X <> 104 //Max 256
> mStartCell = form.ColumnArray[X,1] + LTrim(Str(1))
> mCell = form.ColumnArray[X,1] + LTrim(Str(mRow))
> mEndCell = form.ColumnArray[X,1]+ LTrim(Str(mRow-2))
> mCellRange = mStartCell + ':' + mEndCell
> mThisCell = form.oExcel.ActiveSheet.cells(mRow,X)
> with (mThisCell)
> formula = "=Sum(" + mCellRange + ")"
> endwith
> X = X + 1
> Enddo
> mCellRange = "A1:CZ302" //Max IV302
> form.oExcel.Range(mCellRange).Select()
> form.oExcel.Selection.NumberFormat = ;
> [_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)]
> form.oExcel.Columns("A:CZ").Autofit()
> form.oExcel.Range("A1").Select()
> form.oExcel.visible = .T.
> form.oExcel.quit()
> form.close()
> Endif
> return
>
> function form_onClose
> If GoToIt = .T.
> release object form.oExcel
> Endif
> return
> endclass
>
>

From:	Bruce Beacham
Subject:	Re: Excel closing problem
Date:	Sun, 16 Jan 2005 09:47:52 +0000

Michael Nunn wrote:
> The Excel file gets written OK, but something goes wrong when it is closed.

XP SP2
Plus 2.21 v.1755
Excel 2003

I ran the code and after writing 300 rows it asked me whether I wanted
to save the file (but I had to switch to Excel on the taskbar to see
what had caused the msgbox() sound).

It saved the file and exited from Excel. The task manager then showed
no running Excel process.

This is, I imagine, all as it should be and (being XP) is consistent
with your report.

Bruce Beacham

From:	Michael Nunn
Subject:	Re: Excel closing problem
Date:	Sun, 16 Jan 2005 19:59:04 -0000

Thank you - yes, with XP I have had it working OK. On my machine Excel
appears with the msgbox() - i.e. it doesn't lurk on the task bar.

I have now done further testing (on my laptop using 98SE) to see when things
break down.

To do this, I commented out the formatting commands and the code totalling
the columns from my form script. (I also deleted the redundant varable
initialisation, mTot = 0, and put the zoom command after the sheet select to
tidy it up, though these had no bearing on the problem) .

My findings are:

On single sheet I can fill 16,376 cells. It doesn't matter what combination
of columns and rows I use.

If I start filling cells on a second sheet, between the two sheets I can
fill 16,373 cells - i.e. the second sheet costs 3 cells.

Above those limits I get the problems reported.

The observant in the dBase community may notice that the 266 x 56 report I
mentioned in my initial report is less than 16,376 cells. I didn't mention
that there was another sheet with a smaller report in the same spreadsheet
file, which would have taken it over the apparent limit.

I hope that this helps to identify the cause of the problem.

Mike Nunn

"Bruce Beacham" wrote in message
news:b$$BrC7%23EHA.876@news-server...
> Michael Nunn wrote:
>> The Excel file gets written OK, but something goes wrong when it is
>> closed.
>
> XP SP2
> Plus 2.21 v.1755
> Excel 2003
>
> I ran the code and after writing 300 rows it asked me whether I wanted to
> save the file (but I had to switch to Excel on the taskbar to see what had
> caused the msgbox() sound).
>
> It saved the file and exited from Excel. The task manager then showed
> no running Excel process.
>
> This is, I imagine, all as it should be and (being XP) is consistent with
> your report.
>
>
> Bruce Beacham

From:	Ivar B. Jessen
Subject:	Re: Excel closing problem
Date:	Sun, 16 Jan 2005 12:19:19 +0100

Sat, 15 Jan 2005 18:22:11 -0000 chr(10) dbase.bug-reports chr(10)Excel
closing problem chr(10) "Michael Nunn" chr(10)

>I posted a request for advice on the programming newsgroup but nobody seems
>familiar with my new problem.

Having done further testing and produced some
>data-neutral code to demonstrate the problem, I am posting it here.
[snip]

>I hope this is sufficient information, and look forward to feedback.

I tried you code on Win2000(Danish version)/Plus 2.21/office
2000(Danish version) with the following result,

Running the code as posted results in OLE error on this line,

form.oExcel.Sheets("Sheet1").Select()

It should be either commented out or replaced with,

form.oExcel.Sheets("Ark1").Select()

( As a developer creating programs in a globalized world you knew that
of course )

With the line commented out I got the spreadsheet, saved the file and
the form closed leaving Excel.exe as a process in the task manager.
Repeating this three times more gave me four Excel processes and no
errors or hang ups.

I believe your code has a couple of problems.

The following part may be responsible for the errors or hang ups you
see. The event takes place _after the form is closed and the reference
to Form is probably not accessible at that time.

> function form_onClose
> If GoToIt = .T.
> release object form.oExcel
> Endif
> return

In the following part you forget to set form.oExcel.visible = false
followed by form.oExcel.quit() and form.oExcel = "" as mentioned in my
reply to your posting in the programming group.

> form.oExcel.Range("A1").Select()
> form.oExcel.visible = .T.
> form.oExcel.quit()
> form.close()

But I believe you still have a problem as you have made oExcel a
property of the form. If you instead create oExcel as a 'free
floating' object when opening the form you are free to shut down Excel
whenever you want to instead of waiting until the form is either
closed or released. For example after you have replied to the Excel
save message 'Yes/No/Cancel' you could just shut down oExcel and open
a message box asking if a new report should be created or the form
closed.

I am not saying that it is wrong to have oExcel as a property of the
form, but to me it is much easier to predict the result when
decoupling oExcel from the form.

Ivar B. Jessen

From:	Michael Nunn
Subject:	Re: Excel closing problem
Date:	Sun, 16 Jan 2005 20:32:48 -0000

Thank you, I appreciate this input and will ponder it. I am wondering
though if there are several ways of killing the cat, depending on what we
are actually trying to achieve, as my code (as is) works fine, expect when
the Excel file gets too big (as now further tested and reported in my third
posting in this communication stream).

I did try the form.oExcel = "" (also = null) in my aplication, which didn't
seem to make any difference to my problem in the context. Also, in my
application code I did not explicitly release the object and things still
worked fine. I will give = "" a go on my test routine.

Again, in my application, it is possible to leave the Excel file open (i.e.
on the Excel save/no/cancel message box choose cancel) and on the task bar
even after the form is closed - to be saved later or not as the user may
require. Then of course the instance remains on the list of running
processes and disappears when the instance is closed. Hence, I leave Excel
visible.

I have to confess to not understanding all the mysteries of these
interactions, and had not come across "Ark1" as a substitute for "Sheet1".
Where does one find out these things? I have had no problems hitherto with
using "Sheet1" and am glad to learn that a potential problem is there.

I am interested that you had Excel on the running processes list after you
had closed it. That is not the case when it works properly in my
environment (98SE or XP).

I believe I have established that my fundamental problem is with the
quantity of data, but do take seriously these suggestions and am always
ready to learn from the dBase community, which I find to be a rich fund of
experience and expertise. Again, thank you!

Mike Nunn

"Ivar B. Jessen" wrote in message
news:pviku09nu0pg50ki1kn4a74ct30790621l@4ax.com...
> Sat, 15 Jan 2005 18:22:11 -0000 chr(10) dbase.bug-reports chr(10)Excel
> closing problem chr(10) "Michael Nunn" chr(10)
>
>>I posted a request for advice on the programming newsgroup but nobody
>>seems
>>familiar with my new problem.
>
>
> Having done further testing and produced some
>>data-neutral code to demonstrate the problem, I am posting it here.
> [snip]
>
>>I hope this is sufficient information, and look forward to feedback.
>
> I tried you code on Win2000(Danish version)/Plus 2.21/office
> 2000(Danish version) with the following result,
>
> Running the code as posted results in OLE error on this line,
>
> form.oExcel.Sheets("Sheet1").Select()
>
> It should be either commented out or replaced with,
>
> form.oExcel.Sheets("Ark1").Select()
>
> ( As a developer creating programs in a globalized world you knew that
> of course )
>
> With the line commented out I got the spreadsheet, saved the file and
> the form closed leaving Excel.exe as a process in the task manager.
> Repeating this three times more gave me four Excel processes and no
> errors or hang ups.
>
> I believe your code has a couple of problems.
>
> The following part may be responsible for the errors or hang ups you
> see. The event takes place _after the form is closed and the reference
> to Form is probably not accessible at that time.
>
>> function form_onClose
>> If GoToIt = .T.
>> release object form.oExcel
>> Endif
>> return
>
> In the following part you forget to set form.oExcel.visible = false
> followed by form.oExcel.quit() and form.oExcel = "" as mentioned in my
> reply to your posting in the programming group.
>
>> form.oExcel.Range("A1").Select()
>> form.oExcel.visible = .T.
>> form.oExcel.quit()
>> form.close()
>
> But I believe you still have a problem as you have made oExcel a
> property of the form. If you instead create oExcel as a 'free
> floating' object when opening the form you are free to shut down Excel
> whenever you want to instead of waiting until the form is either
> closed or released. For example after you have replied to the Excel
> save message 'Yes/No/Cancel' you could just shut down oExcel and open
> a message box asking if a new report should be created or the form
> closed.
>
> I am not saying that it is wrong to have oExcel as a property of the
> form, but to me it is much easier to predict the result when
> decoupling oExcel from the form.
>
>
> Ivar B. Jessen

From:	Ivar B. Jessen
Subject:	Re: Excel closing problem
Date:	Mon, 17 Jan 2005 12:40:26 +0100

Sun, 16 Jan 2005 20:32:48 -0000 chr(10) dbase.bug-reports chr(10)Re:
Excel closing problem chr(10) "Michael Nunn"
chr(10)

>Thank you, I appreciate this input and will ponder it. I am wondering
>though if there are several ways of killing the cat, depending on what we
>are actually trying to achieve, as my code (as is) works fine, expect when
>the Excel file gets too big (as now further tested and reported in my third
>posting in this communication stream).

The code below creates a spreadsheet with up to three sheets. I tried
it with three sheets each with 300 rows and 256 columns and didn't see
any problems, probably as it didn't include all the details in your
code.

>
>I did try the form.oExcel = "" (also = null) in my aplication, which didn't
>seem to make any difference to my problem in the context. Also, in my
>application code I did not explicitly release the object and things still
>worked fine. I will give = "" a go on my test routine.
>
>Again, in my application, it is possible to leave the Excel file open (i.e.
>on the Excel save/no/cancel message box choose cancel) and on the task bar
>even after the form is closed - to be saved later or not as the user may
>require. Then of course the instance remains on the list of running
>processes and disappears when the instance is closed. Hence, I leave Excel
>visible.

When I said that I ran your code and saved the sheet it is not quite
correct. When the seet opended I pressed the cancel button to inspect
the sheet, ie how many rows and columns were created, and then saved
the sheet, in that case the processes did not close. When saving
directly from the messagebox the processes closed.

>I have to confess to not understanding all the mysteries of these
>interactions, and had not come across "Ark1" as a substitute for "Sheet1".
>Where does one find out these things? I have had no problems hitherto with
>using "Sheet1" and am glad to learn that a potential problem is there.

The word 'ARK' is the Danish word for 'Sheet'. The MS office programs
in non-English versions sometimes have problems using for example
English language macros because some of the terms are not translated
automatically. You will neve meet the problem if your code is running
with English versions only ;-)

>I am interested that you had Excel on the running processes list after you
>had closed it. That is not the case when it works properly in my
>environment (98SE or XP).

Please se explanation above. I couldn't imagine anyone saving a large
spreadsheet without first checking if it looked ok. If you are forced
to save it blindly when using the Excel msgbox you might as well force
the saving also.
>
>I believe I have established that my fundamental problem is with the
>quantity of data, but do take seriously these suggestions and am always
>ready to learn from the dBase community, which I find to be a rich fund of
>experience and expertise. Again, thank you!

Will your code also have problems if you create the sheet(s) in the
way I do it in the code below?

Ivar B. Jessen

//-----
** END HEADER -- do not remove this line
//
// Generated on 17-01-2005
//
parameter bModal
local f
f = new fillUpExcelForm()
if (bModal)
f.mdi = false // ensure not MDI
f.readModal()
else
f.open()
endif

class fillUpExcelForm of FORM
with (this)
height = 7.9091
left = 37.0
top = 0.0
width = 33.5714
text = ""
mdi = true
endwith

this.SPINBOX1 = new SPINBOX(this)
with (this.SPINBOX1)
height = 1.0
left = 23.0
top = 1.0
width = 8.0
rangeMax = 65635
rangeMin = 1
value = 1
rangeRequired = true
endwith

this.SPINBOX2 = new SPINBOX(this)
with (this.SPINBOX2)
height = 1.0
left = 23.0
top = 2.5
width = 8.0
rangeMax = 256
rangeMin = 1
value = 1
rangeRequired = true
endwith

this.TEXTLABEL1 = new TEXTLABEL(this)
with (this.TEXTLABEL1)
height = 1.0
left = 1.0
top = 1.0
width = 21.0
text = "Set number of rows"
endwith

this.TEXTLABEL2 = new TEXTLABEL(this)
with (this.TEXTLABEL2)
height = 1.0
left = 1.0
top = 2.5
width = 22.0
text = "Set number of columns"
endwith

this.PUSHBUTTON1 = new PUSHBUTTON(this)
with (this.PUSHBUTTON1)
onClick = class::PUSHBUTTON1_ONCLICK
height = 1.0909
left = 1.0
top = 6.0
width = 31.0
text = "Make new report"
endwith

this.SPINBOX3 = new SPINBOX(this)
with (this.SPINBOX3)
height = 1.0
left = 23.0
top = 4.0
width = 8.0
rangeMax = 3
rangeMin = 1
value = 1
rangeRequired = true
endwith

this.TEXTLABEL3 = new TEXTLABEL(this)
with (this.TEXTLABEL3)
height = 1.0
left = 1.0
top = 4.0
width = 21.0
text = "Set number of sheets"
endwith

function PUSHBUTTON1_onClick

this.enabled = false

nSheet = form.spinbox3.value

ival = form.spinbox1.value
jval = form.spinbox2.value

form.oExcel = new oleAutoclient("Excel.Application")
form.oExcel.Workbooks.Add()

form.oExcel.Sheets("Sheet1").Select()
for i = 1 to ival
for j = 1 to jval
oCell = form.oExcel.ActiveSheet.cells( i, j )
oCell.formula = (10 * j) + 1
next j
next i

if nSheet = 2 or nSheet = 3
form.oExcel.Sheets("Sheet2").Select()
for i = 1 to ival
for j = 1 to jval
oCell = form.oExcel.ActiveSheet.cells( i, j )
oCell.formula = (10 * j) + 2
next j
next i
endif

if nSheet = 3
form.oExcel.Sheets("Sheet3").Select()
for i = 1 to ival
for j = 1 to jval
oCell = form.oExcel.ActiveSheet.cells( i, j )
oCell.formula = (10 * j) + 3
next j
next i
endif

form.oExcel.visible = true
this.enabled = true
return
endclass
//-----

From:	Michael Nunn
Subject:	Re: Excel closing problem
Date:	Mon, 17 Jan 2005 23:16:41 -0000

Iva Jessen wrote, for which, again, much thanks:

> The word 'ARK' is the Danish word for 'Sheet'.

Det skulle jag kanske ha vetat - det är detsamma på svenska (förlåt!).

>
> The code below creates a spreadsheet with up to three sheets. I tried
> it with three sheets each with 300 rows and 256 columns and didn't see
> any problems, probably as it didn't include all the details in your
> code.

This is neat code. I have run it (I had to add commands to add sheets 2 and
3 as I have Excel opening with only one sheet), but in my environment get
results consistent with my own code. i.e I still seem to get the same
problems if I exceed my viable cell count (16,376). So it is possibly a
problem only occurring in the 98SE context. As mentioned previously , I
first noted the problem on my work PC running 98SE, though I haven't run the
cell count test on that machine.

I have established an additional point - blank Excel cells don't count. i.e
If I skip every alternate row I can get to row 127 (part way along) before I
hit the problem.

>Please see explanation above. I couldn't imagine anyone saving a large
>spreadsheet without first checking if it looked ok. If you are forced
>to save it blindly when using the Excel msgbox you might as well force
>the saving also.

I am not sure what your point is here. In my routines writing to Excel,
Excel is visible and appears. The user then disposes of it as he/she
wishes.

Unfortunately, my problem is unresolved. However, now that I am clear about
the threshhold, I can introduce code to check the number of of cells written
and have appropriate messages.

This issue is posted under bug reports, because I appear to have identified
a problem with the interaction between dBase and Excel with 98SE. I guess
if someone else could run Ivar's and my routines with 98SE this might be
verified.

Mike Nunn

I have also established

From:	Ivar B. Jessen
Subject:	Re: Excel closing problem
Date:	Tue, 18 Jan 2005 17:00:28 +0100

Mon, 17 Jan 2005 23:16:41 -0000 chr(10) dbase.bug-reports chr(10)Re:
Excel closing problem chr(10) "Michael Nunn"
chr(10)

>> The word 'ARK' is the Danish word for 'Sheet'.
>
>Det skulle jag kanske ha vetat - det är detsamma på svenska (förlåt!).

Prøv en svensk version af Excel ;-)

>> The code below creates a spreadsheet with up to three sheets. I tried
>> it with three sheets each with 300 rows and 256 columns and didn't see
>> any problems, probably as it didn't include all the details in your
>> code.
>
>This is neat code. I have run it (I had to add commands to add sheets 2 and
>3 as I have Excel opening with only one sheet), but in my environment get
>results consistent with my own code. i.e I still seem to get the same
>problems if I exceed my viable cell count (16,376). So it is possibly a
>problem only occurring in the 98SE context. As mentioned previously , I
>first noted the problem on my work PC running 98SE, though I haven't run the
>cell count test on that machine.

[snip]

>Unfortunately, my problem is unresolved. However, now that I am clear about
>the threshhold, I can introduce code to check the number of of cells written
>and have appropriate messages.
>
>This issue is posted under bug reports, because I appear to have identified
>a problem with the interaction between dBase and Excel with 98SE. I guess
>if someone else could run Ivar's and my routines with 98SE this might be
>verified.

I tried my code on Win98SE/64MB Ram.

With one sheet, 200 rows and 128 columns it runs fine.
With one sheet, 250 rows and 128 columns dBase hangs with no errors.
When opening the Task Manager it says 'Dbase not responding', 'Excel
not responding'.

Ivar B. Jessen

From:	Michael Nunn
Subject:	Re: Excel closing problem
Date:	Tue, 18 Jan 2005 18:47:18 -0000

Ivar B. Jessen wrote:

> I tried my code on Win98SE/64MB Ram.
>
> With one sheet, 200 rows and 128 columns it runs fine.
> With one sheet, 250 rows and 128 columns dBase hangs with no errors.
> When opening the Task Manager it says 'Dbase not responding', 'Excel
> not responding'.

And I have now tested your routine on my office PC (again 98SE). Using 1
sheet, 1 column and 16,377 rows, it works. Over that the problem occurs.
i.e. It gave me one cell more than my laptop.

Your set-up seems to have a higher break-point, but your break symptoms are
consistent with mine.

I am not sure if DBI is interested in this issue. It is in fact a pretty
awkward problem for my main application, which uses Excel as the primary
report outlet.

Mike Nunn

From:	Ivar B. Jessen
Subject:	Re: Excel closing problem
Date:	Wed, 19 Jan 2005 15:12:44 +0100

Tue, 18 Jan 2005 18:47:18 -0000 chr(10) dbase.bug-reports chr(10)Re:
Excel closing problem chr(10) "Michael Nunn"
chr(10)

Micheal,

I believe I found the reason for the poor behaviour in Win98.

It looks like it is a sort of timing problem. Excel apparently needs a
certain time to digest each new cell value received from dBL. If dBL
feeds new values to Excel at a higher rate that Excel can accept,
things go wrong. The cure is to introduce a small delay after
inserting a new cell value, for example with sleep command, a looop or
by taking some other action before returning to the the next new
value.

>> I tried my code on Win98SE/64MB Ram.
>>
>> With one sheet, 200 rows and 128 columns it runs fine.
>> With one sheet, 250 rows and 128 columns dBase hangs with no errors.
>> When opening the Task Manager it says 'Dbase not responding', 'Excel
>> not responding'.
>
>And I have now tested your routine on my office PC (again 98SE). Using 1
>sheet, 1 column and 16,377 rows, it works. Over that the problem occurs.
>i.e. It gave me one cell more than my laptop.

In order to see at which number of rows/colummns my form failed I
added two textlabels at the right of the spinboxes and set their text
values with the following lines:

form.oExcel.Sheets("Ark1").Select()
for i = 1 to ival
form.textlabel4.text := i // <--- new line
for j = 1 to jval
oCell = form.oExcel.ActiveSheet.cells( i, j )
oCell.formula = (10 * j) + 1
form.textlabel5.text := j // <--- new line
next j
next i

With this simple amendment the form did not fail in Win98SE at 250
rows and 127 columns as before ;-) I was in fact able to fill 1000
rows x 225 columns without getting any hang-ups or other problems.

>Your set-up seems to have a higher break-point, but your break symptoms are
>consistent with mine.

The CPU is an AMD-K6 233 MHz running at 200 MHz and with the added
delay of writing the row and column numbers to the textLabelFields
Excel gets the necessary time to do it's thing. You have probably a
faster CPU and as Excel thus have less time to digest the new cells
created the problem occurs at an earlier point.

>I am not sure if DBI is interested in this issue. It is in fact a pretty
>awkward problem for my main application, which uses Excel as the primary
>report outlet.

It is still a bug if the same code works on a 32bit OS and not on a
16bit OS.

If you use both Win98 and XP you should probably test first for type
of OS and then insert suitable delays when running on Win98.

I hope you can reproduce my results.

Ivar B. Jessen

From:	Michael Nunn
Subject:	Re: Excel closing problem
Date:	Wed, 19 Jan 2005 22:38:38 -0000

Yes - you may be onto something. But I am having to put a rather severe
break point between cell writes. I am currently experimenting with
counting loops, seeing how low a count I can get away with.. Just having
row/cell counters, even a cumulative cell counter, on the go isn't enough.

I also suspect there are two stages to the Excel choking. Your routine came
to a complete halt while writing a larger number of cells. But I had not
noted that problem. It was only that after writing more than 16,376 cells I
would have a problem if I tried to run the routine again without tidying up
the task manager first - i.e. Excel would still be showing as running after
it had been closed.

I continue to test your hypothesis.

Mike

"Ivar B. Jessen" wrote in message
news:6kosu014h77iljkr8cgns737u4oo3bc9f8@4ax.com...
> Tue, 18 Jan 2005 18:47:18 -0000 chr(10) dbase.bug-reports chr(10)Re:
> Excel closing problem chr(10) "Michael Nunn"
> chr(10)
>
> Micheal,
>
> I believe I found the reason for the poor behaviour in Win98.
>
> It looks like it is a sort of timing problem. Excel apparently needs a
> certain time to digest each new cell value received from dBL. If dBL
> feeds new values to Excel at a higher rate that Excel can accept,
> things go wrong. The cure is to introduce a small delay after
> inserting a new cell value, for example with sleep command, a looop or
> by taking some other action before returning to the the next new
> value.
>
>>> I tried my code on Win98SE/64MB Ram.
>>>
>>> With one sheet, 200 rows and 128 columns it runs fine.
>>> With one sheet, 250 rows and 128 columns dBase hangs with no errors.
>>> When opening the Task Manager it says 'Dbase not responding', 'Excel
>>> not responding'.
>>
>>And I have now tested your routine on my office PC (again 98SE). Using 1
>>sheet, 1 column and 16,377 rows, it works. Over that the problem occurs.
>>i.e. It gave me one cell more than my laptop.
>
> In order to see at which number of rows/colummns my form failed I
> added two textlabels at the right of the spinboxes and set their text
> values with the following lines:
>
> form.oExcel.Sheets("Ark1").Select()
> for i = 1 to ival
> form.textlabel4.text := i // <--- new line
> for j = 1 to jval
> oCell = form.oExcel.ActiveSheet.cells( i, j )
> oCell.formula = (10 * j) + 1
> form.textlabel5.text := j // <--- new line
> next j
> next i
>
> With this simple amendment the form did not fail in Win98SE at 250
> rows and 127 columns as before ;-) I was in fact able to fill 1000
> rows x 225 columns without getting any hang-ups or other problems.
>
>>Your set-up seems to have a higher break-point, but your break symptoms
>>are
>>consistent with mine.
>
> The CPU is an AMD-K6 233 MHz running at 200 MHz and with the added
> delay of writing the row and column numbers to the textLabelFields
> Excel gets the necessary time to do it's thing. You have probably a
> faster CPU and as Excel thus have less time to digest the new cells
> created the problem occurs at an earlier point.
>
>>I am not sure if DBI is interested in this issue. It is in fact a pretty
>>awkward problem for my main application, which uses Excel as the primary
>>report outlet.
>
> It is still a bug if the same code works on a 32bit OS and not on a
> 16bit OS.
>
> If you use both Win98 and XP you should probably test first for type
> of OS and then insert suitable delays when running on Win98.
>
> I hope you can reproduce my results.
>
>
> Ivar B. Jessen

From:	Michael Nunn
Subject:	Re: Excel closing problem
Date:	Thu, 20 Jan 2005 08:16:09 -0000

Continued ........

Re Ivar Jessen diagnosis:

With a loop counting to 50 between cell writes I have stretched to 76,800
cells without the problem occurring, so you seem to have cracked it.

It is a significant sacrifice of speed though, and evidently not necessary
with Windows XP.

Is there a way of testing for the Windows version from within a dB
programme?

Mike

"Michael Nunn" wrote in message
news:jjVdbfn$EHA.1128@news-server...
>
>
>
>
>
>
>
>
> "Ivar B. Jessen" wrote in message
> news:6kosu014h77iljkr8cgns737u4oo3bc9f8@4ax.com...
>> Tue, 18 Jan 2005 18:47:18 -0000 chr(10) dbase.bug-reports chr(10)Re:
>> Excel closing problem chr(10) "Michael Nunn"
>> chr(10)
>>
>> Micheal,
>>
>> I believe I found the reason for the poor behaviour in Win98.
>>
>> It looks like it is a sort of timing problem. Excel apparently needs a
>> certain time to digest each new cell value received from dBL. If dBL
>> feeds new values to Excel at a higher rate that Excel can accept,
>> things go wrong. The cure is to introduce a small delay after
>> inserting a new cell value, for example with sleep command, a looop or
>> by taking some other action before returning to the the next new
>> value.
>>
>>>> I tried my code on Win98SE/64MB Ram.
>>>>
>>>> With one sheet, 200 rows and 128 columns it runs fine.
>>>> With one sheet, 250 rows and 128 columns dBase hangs with no errors.
>>>> When opening the Task Manager it says 'Dbase not responding', 'Excel
>>>> not responding'.
>>>
>>>And I have now tested your routine on my office PC (again 98SE). Using 1
>>>sheet, 1 column and 16,377 rows, it works. Over that the problem occurs.
>>>i.e. It gave me one cell more than my laptop.
>>
>> In order to see at which number of rows/colummns my form failed I
>> added two textlabels at the right of the spinboxes and set their text
>> values with the following lines:
>>
>> form.oExcel.Sheets("Ark1").Select()
>> for i = 1 to ival
>> form.textlabel4.text := i // <--- new line
>> for j = 1 to jval
>> oCell = form.oExcel.ActiveSheet.cells( i, j )
>> oCell.formula = (10 * j) + 1
>> form.textlabel5.text := j // <--- new line
>> next j
>> next i
>>
>> With this simple amendment the form did not fail in Win98SE at 250
>> rows and 127 columns as before ;-) I was in fact able to fill 1000
>> rows x 225 columns without getting any hang-ups or other problems.
>>
>>>Your set-up seems to have a higher break-point, but your break symptoms
>>>are
>>>consistent with mine.
>>
>> The CPU is an AMD-K6 233 MHz running at 200 MHz and with the added
>> delay of writing the row and column numbers to the textLabelFields
>> Excel gets the necessary time to do it's thing. You have probably a
>> faster CPU and as Excel thus have less time to digest the new cells
>> created the problem occurs at an earlier point.
>>
>>>I am not sure if DBI is interested in this issue. It is in fact a pretty
>>>awkward problem for my main application, which uses Excel as the primary
>>>report outlet.
>>
>> It is still a bug if the same code works on a 32bit OS and not on a
>> 16bit OS.
>>
>> If you use both Win98 and XP you should probably test first for type
>> of OS and then insert suitable delays when running on Win98.
>>
>> I hope you can reproduce my results.
>>
>>
>> Ivar B. Jessen
>
>

From:	Ivar B. Jessen
Subject:	Re: Excel closing problem
Date:	Thu, 20 Jan 2005 11:45:44 +0100

Thu, 20 Jan 2005 08:16:09 -0000 chr(10) dbase.bug-reports chr(10)Re:
Excel closing problem chr(10) "Michael Nunn"
chr(10)

>
>With a loop counting to 50 between cell writes I have stretched to 76,800
>cells without the problem occurring, so you seem to have cracked it.

Thanks for the confirmation.

>It is a significant sacrifice of speed though, and evidently not necessary
>with Windows XP.

To avoid the problems with Win98 you might consider writing the
row/column results out to a semicolon separated file
and import that file file into Excel using Ole?

>Is there a way of testing for the Windows version from within a dB
>programme?

See the OLH on OS().

Ivar B. Jessen

From:	Michael Nunn
Subject:	Re: Excel closing problem
Date:	Thu, 20 Jan 2005 18:43:36 -0000

Ivar Jessen wrote:

> To avoid the problems with Win98 you might consider writing the
> row/column results out to a semicolon separated file
> and import that file file into Excel using Ole?

Then I would have gone full circle. Until I learned that it was possible to
write directly from dB to Excel, I 'listed' everything from a suitably
organised dbf to a text file and then imported it into Excel. I had trouble
with the list command and unwanted zeroes (I submitted a bug report about
this, which is on the bug record), which had to be tidied up in Excel
Apart from that, I had manually to do the formatting, totalling, etc. The
neat thing about writing directly to Excel is that you can deliver the
finished article, formatted and with formulas. So I think I will stick with
the direct write, even if I have to slow it down.

> See the OLH on OS().

Will do.

Many thanks.

Mike

From:	Ronnie MacGregor
Subject:	Re: Excel closing problem
Date:	Wed, 19 Jan 2005 17:09:54 -0000

In article <6kosu014h77iljkr8cgns737u4oo3bc9f8@4ax.com>,
bergishagen@it.notthis.dk says...

> The CPU is an AMD-K6 233 MHz running at 200 MHz

Starting a new trend of under clocking ?

--
Ronnie MacGregor
Scotland

From:	Ivar B. Jessen
Subject:	Re: Excel closing problem
Date:	Wed, 19 Jan 2005 21:04:00 +0100

Wed, 19 Jan 2005 17:09:54 -0000 chr(10) dbase.bug-reports chr(10)Re:
Excel closing problem chr(10) Ronnie MacGregor
chr(10)

>> The CPU is an AMD-K6 233 MHz running at 200 MHz
>
>Starting a new trend of under clocking ?

It was not intended ;-) I needed a computer with ISA slots for
additional parallel ports and the only working motherboard I had did
not allow more than 200MHz. It is mostly used for running a DOS
program, so speed is not an issue here.

Ivar B. Jessen