Naomi

I've incorporated this in my system and it works a treat. I guess, both from the other contibutors' comments, and my exprerience with the different techniques, this worked out certainly the fastest, as it makes use of the code already written, which EXPORTs to XLS, quicker than the copying and pasting to sheet ranges.

Great, thanks.

Thanks again to the others - their contibs are all stored away for future use.

Mind you, I've still got to put the functions in the cells yet - next task.

Terry


>>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:
>
>

>************************************************************
>*  Function CombineExcelFiles
>************************************************************
>*  Author............: VCS  Developers Team
>*  Project...........: Visual Collections System
>*  Created...........: Craig Boyd 3/6/2006  23:55:50
>*) Description.......:
>*  Calling Samples...: DIMENSION aXLSFiles(3)
>*!*					aXLSFiles(1) = "C:\temp1.xls"
>*!*					aXLSFiles(2) = "C:\temp2.xls"
>*!*					aXLSFiles(3) = "C:\temp3.xls"
>*!*					CombineExcelFiles(@aXLSFiles, "C:\XLSCombined.xls")
>*  Parameter List....:
>*  Major change list.:
>function CombineExcelFiles (taXLSFiles, tcDestination, tlDeleteOriginal)
>external array taXLSFiles
>local loExcel as Excel.application, ;
>	loWorkBook as Excel.Worksbook, ;
>	loWorkSheet , ;
>	lnCounter, lcWorkSheetCaption, lcError, ;
>	lcValidChars
>
>lcError = ""
>
>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?