>>Hi guys
>>
>>I need to do 2 things:
>>
>>1) from my current cursor I need to produce 3 worksheets in the same book, each being more of a summary of the previous.
>>i.e. I have a cursor with all the data, cut down to sub-totals and totals, and cut down to just totals (and a grand total for each, of course). I know how to reduce the cursor for each sheet, but how to include each one in the same book.
>>
>
>For a similar task I produce three separate spreadsheets and then combine them using the following routine:
Thanks a lot, Nadia. I'll have a look at that in detail presently. Just one thing: I'm still on VFP7 and thus don't have the Try ... Catch facility (and not sure what it's all about, for that matter).
Terry
...
>
>try
> lcValidChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789 "
> loExcel = newobject("Excel.Application")
> with loExcel
> .ScreenUpdating = .f.
> .DisplayAlerts = .f.
> .WorkBooks.add()
> lnCounter = 0
>
>** Delete all existing worksheets except 1
> for each loWorkSheet in .WorkBooks(1).WorkSheets
> lnCounter = m.lnCounter + 1
> if m.lnCounter > 1
> loWorkSheet.delete
> endif
> endfor
>
> for lnCounter = 1 to alen(taXLSFiles,1)
> if file(taXLSFiles[m.lnCounter])
> lcWorkSheetCaption = juststem(taXLSFiles[m.lnCounter])
> loWorkBook = .WorkBooks.open(taXLSFiles[m.lnCounter])
> loWorkBook.WorkSheets(1).copy(null, ;
> .WorkBooks(1).WorkSheets(.WorkBooks(1).WorkSheets.count))
> .WorkBooks(1).ActiveSheet.name = ;
> right(alltrim(chrtran(m.lcWorkSheetCaption, ;
> chrtran(m.lcWorkSheetCaption,m.lcValidChars,"")," ")), 31) &&loWorkBook.Name
> loWorkBook.close(.f.) && Don't save changes
> if m.tlDeleteOriginal
> erase (taXLSFiles[m.lnCounter])
> endif
> endif
> endfor
>** Remove the first original sheet from (Sheet1)
> .WorkBooks(1).WorkSheets(1).delete
>
> .WorkBooks(1).saveas(m.tcDestination)
> .ScreenUpdating = .t.
> .DisplayAlerts = .t.
> endwith
>
>catch to loError
> lcError = Log_Error(m.loError)
>finally
> if vartype(m.loExcel) = 'O'
> with loExcel
> .ScreenUpdating = .t.
> .DisplayAlerts = .t.
> .quit()
> endwith
> endif
>endtry
>
>return m.lcError
>endfunc
>
>
>>2) VFP currently does the above totals/sub-totals but my users would prefer the SUM() function, as they may need to insewrt extra rows in the s/s/ afterwards. I imagine this is a bit tricky.
>>
>>A tall order?
>>
>>Thanks for listening
>>
>>Terry
>
>Not sure I understand the second part. What exactly is the problem?
- Whoever said that women are the weaker sex never tried to wrest the bedclothes off one in the middle of the night
- Worry is the interest you pay, in advance, for a loan that you may never need to take out.