>Hi All:
>
>I'm trying to process an Excel spreadsheet via VFP 9. I've got a main form which sets up some parameters and an OK button to start the processing. In the OK button I have
>oxl = CREATEOBJECT("excel.application")
>oxl.displayalerts = .F.
>oxl.workbooks.add
>owb = oxl.activeworkbook
>.
>.
>.
>If thisform.chkDetail.value
> thisform.mCreDtl
> thisform.mCreDtlMYR
>ENDIF
>.
>other similar calls.
>.
>
In the beginning of each of the two methods I have
>owb.Sheets.Add
>osh = owb.ActiveSheet
>osh.name = 'Creditors Detail'
>
>r1 = 1
>rb = r1
>
>
>WITH osh
> etc
>
>The two methods are very similar and process roughly the same amount of data. The first one (mCreDtl) is slow, but not unreasonable -- about 2 minutes.
>The second one (mCreDtlMYR) takes almost fifteen minutes to complete.
>
>Because the two methonds are not dependent on each other, I can reverse the sequence in which they are called.
>I do that with the same results. The first one to run (mCreDtlMYR) is slow, but not unreasonable.
>The second one (mCreDtl) takes almost fifteen minutes to complete.
>
>I tried forcing osh to NULL at the end of the methods, but that had no impact.
>I've also tried a FLUSH and SYS(1104), but that had no impact.
>
>My only thought at the moment is to save each worksheet in a separate file, close XL at the end of each method and then try to combine them into one spreadsheet
>once the individual methods have completed.
>
>Is there other approach I can take to get the processing up to speed?
>
>Thanks to all ..........Rich
HI Rich
Are you closing the reference to osh in the first method, before reusing / recreating it in the second. I think the syntax is osh = null, not positive. You might also want to save the sheet between methods. Speed is usually increased by setting calculation to manual before starting modification.
Bill Sutton