Once again off the top off my head:)

with oExcel
.DisplayAlerts = .f.
*...
.ActiveWorkbook.SaveAs("fullpath of new file here", xlWorkbookNormal)
.ActiveWorkbook.Save
* .Quit
endwith

First where you go wrong. You need to open source workbook before copying.

oSource = .Workbooks.Open(m.lcFile)

.WorkBooks.Add

oSource.WorkSheets....Copy( ... )

Tomorrow I'll post a series of codes doing that in different ways (all were data transferring tests). As I remember the fastest 2 ways were:

1) Open parts of it (which would be different sheets in a single workbook) in different workbooks and visually tile their windows. In other words do not collect in a single wb.

2) Copy parts tab delimited. FileTostr() and place in clipboard. Paste onto target sheets in a single workbook.

All the other ways, though sounded more elegant codewise, were simply much more slower. There are faster ones in this group (like CopyFromRecordset) but still the fastest one of them were at least 30 times slower as I remember. IMHO excel is terrible copying cells from somewhere. It's slow and gets somewhat logarithmically slower as number of spreadsheets increase.

Anyway tomorrow if I can locate test codes I'll post them so you can test yourself and maybe find where I go wrong in those codes and discover a fast way. The one thing I haven't tried nor did find time to try is to create whole Excel file in code as XML. I expect it to be the fastest way ever but not sure (HTML might be an option too).

OK here are the samples as I promised. Keep in mind that they were 'test' codes. I'll post them 1 per message so it'd be easier to follow. Benchmark yourself.

Clear All
* Create a test cursor
Create Cursor testcursor (Rcno i,cType c(10), nType i, dType d)
Rand(-1)
For ix = 1 To 200000 && Create 200000 recs cursor
Insert Into testcursor Values ;
(Recco()+1,Sys(2015), Int(Rand()*1000), Date()-Int(Rand()*100))
Endfor

Set Sysformats On
Set Safety Off

lnTotal = Reccount()
Wait Window Nowait "Pls wait sending data to Excel..."
Start = Seconds()
oExcel = Createobject("Excel.application")
With oExcel
With .workbooks.Add && Add a new workbook - copy onto this one
lnMaxRows = .ActiveSheet.Rows.Count && Get max row count
lnNeededBooks = Ceiling( m.lnTotal / (m.lnMaxRows - 1) ) && 1 row header
"Total Books : "+Padr(m.lnNeededBooks,3," ")
If .Sheets.Count < m.lnNeededBooks
.sheets.Add(,.sheets(.Sheets.Count),;
m.lnNeededBooks - .Sheets.Count) && Add new sheets after old ones
Endif
Endwith
oTargetBook = .ActiveWorkBook
For ix = 1 To m.lnNeededBooks
lnStart = ( m.ix - 1 ) * (m.lnMaxRows-1) + 1
Copy To ("temp"+Padl(m.ix,2,"0")) ;
for Between(Recno(),m.lnStart,m.lnStart+m.lnMaxRows-2) ;
type Fox2x
.workbooks.Open(Sys(5)+Curdir()+"temp"+Padl(m.ix,2,"0"))
.ActiveWorkBook.ActiveSheet.UsedRange.Copy( oTargetBook.WorkSheets(m.ix).Range('A1') )
.ActiveWorkBook.Close(.F.)
"Book no : "+;
padl(ix,3," ")+;
"/"+;
padr(m.lnNeededBooks,3," ")+ ;
" Elapsed : ",Seconds() - m.start
Endfor
.Visible = .T.
Endwith
"Total Elapsed : ",Seconds() - Start

In this one note that you might skip 'VFP copy part' but just kept that to signify it's not VFP slowing this down.

lnTotal = 200000
* Create a test cursor
Create Table (ADDBS(SYS(2023))+'xltest') (Rcno i,cType c(10), nType i, dType d)
Rand(-1)
For ix = 1 To m.lnTotal && Create 200000 recs cursor
Insert Into xlTest Values ;
(Recco()+1,Sys(2015), Int(Rand()*1000), Date()-Int(Rand()*100))
ENDFOR
USE IN 'xlTest'

Start = Seconds()
lnMaxRows = 65536
lnNeededSheets = Ceiling( m.lnTotal / (m.lnMaxRows - 1) ) && 1 row header

Local lcConStr,lcPrefix,loRS As 'ADODB.RecordSet'
lcConStr = "Provider=VFPOLEDB;Data Source="+Sys(2023)
Local Array aRS[m.lnNeededSheets]
For ix = 1 To m.lnNeededSheets
lnStart = ( m.ix - 1 ) * (m.lnMaxRows-1) + 1
aRS[m.ix] = Forcepath(Textmerge('xlPart<<m.ix>>.rs'),Sys(2023))
SaveAsRS( m.lcConStr, aRS[m.ix], ;
TEXTMERGE('select * from xlTest'+;
' where RECNO() between <<m.lnStart>> and <<m.lnStart+m.lnMaxRows-2>>'))
Endfor
"VFP saved recordsets",Seconds()-m.start

loRS = Createobject("AdoDb.RecordSet")
oExcel = Createobject("Excel.Application")
With oExcel
.WorkBooks.Add
.Visible = .T.
With .ActiveWorkBook
If .Sheets.Count < m.lnNeededSheets
.sheets.Add(,.sheets(.Sheets.Count),;
m.lnNeededSheets - .Sheets.Count) && Add new sheets after old ones
Endif
For ix = 1 To m.lnNeededSheets
.WorkSheets(m.ix).Activate
loRS.Open( aRS[m.ix] )
.WorkSheets(m.ix).QueryTables.Add( loRS, ;
.WorkSheets(m.ix).Range('A1')).Refresh()
loRS.Close
Erase (aRS[m.ix])
"Excel sheet",m.ix,Seconds()-m.start
Endfor
Endwith
Endwith
"Total",m.ix,Seconds()-m.start

Function SaveAsRS(tcConnString, tcRSFileName, tcSQL)
Local loConn,loRS
loConn = Createobject("Adodb.connection")
loConn.ConnectionString = m.tcConnString
loConn.Open
loRS = loConn.Execute(m.tcSQL)
loRS.Save(m.tcRSFileName)
loRS.Close
loConn.Close
Endfunc

Similar to previous one.

lnTotal = 200000
* Create a test cursor
Create Table (ADDBS(SYS(2023))+'xltest') (Rcno i,cType c(10), nType i, dType d)
Rand(-1)
For ix = 1 To m.lnTotal && Create 200000 recs cursor
Insert Into xlTest Values ;
(Recco()+1,Sys(2015), Int(Rand()*1000), Date()-Int(Rand()*100))
ENDFOR
USE IN 'xlTest'

Start = Seconds()
lnMaxRows = 65536
lnNeededSheets = Ceiling( m.lnTotal / (m.lnMaxRows - 1) ) && 1 row header

Local lcConStr,lcPrefix,loRS As 'ADODB.RecordSet'
lcConStr = "Provider=VFPOLEDB;Data Source="+Sys(2023)
Local Array aRS[m.lnNeededSheets]
For ix = 1 To m.lnNeededSheets
lnStart = ( m.ix - 1 ) * (m.lnMaxRows-1) + 1
aRS[m.ix] = Forcepath(Textmerge('xlPart<<m.ix>>.rs'),Sys(2023))
SaveAsRS( m.lcConStr, aRS[m.ix], ;
TEXTMERGE('select * from xlTest'+;
' where RECNO() between <<m.lnStart>> and <<m.lnStart+m.lnMaxRows-2>>'))
Endfor
"VFP saved recordsets",Seconds()-m.start

loRS = Createobject("AdoDb.RecordSet")
oExcel = Createobject("Excel.Application")
With oExcel
.WorkBooks.Add
With .ActiveWorkBook
If .Sheets.Count < m.lnNeededSheets
.sheets.Add(,.sheets(.Sheets.Count),;
m.lnNeededSheets - .Sheets.Count) && Add new sheets after old ones
Endif
For ix = 1 To m.lnNeededSheets
.WorkSheets(m.ix).Activate
loRS.Open( aRS[m.ix] )
.WorkSheets(m.ix).Range('A1').CopyFromRecordSet( loRS )
loRS.Close
Erase (aRS[m.ix])
"Excel sheet",m.ix,Seconds()-m.start
Endfor
Endwith
.Visible = .T.
Endwith
"Total",m.ix,Seconds()-m.start

Function SaveAsRS(tcConnString, tcRSFileName, tcSQL)
Local loConn,loRS
loConn = Createobject("Adodb.connection")
loConn.ConnectionString = m.tcConnString
loConn.Open
loRS = loConn.Execute(m.tcSQL)
loRS.Save(m.tcRSFileName)
loRS.Close
loConn.Close
Endfunc

One of the fast ones:

clear
Clear All
* Create a test cursor
Create Cursor testcursor (Rcno i,cType c(10), nType i, dType d)
Rand(-1)
For ix = 1 To 200000 && Create 200000 recs cursor
Insert Into testcursor Values ;
(Recco()+1,Sys(2015), Int(Rand()*1000), Date()-Int(Rand()*100))
Endfor
lcHeader = Chrtran('Rcno,cType,nType,dType',',',Chr(9))+Chr(13)+Chr(10)

#Define xlDelimited 1
Set Sysformats On
Set Safety Off

lnTotal = Reccount()
locate
Wait Window Nowait "Pls wait sending data to Excel..."
Start = Seconds()
oExcel = Createobject("Excel.application")
With oExcel
.workbooks.Add && Add a new workbook
lnMaxRows = .ActiveWorkBook.ActiveSheet.Rows.Count && Get max row count
lnNeededSheets = Ceiling( lnTotal / (lnMaxRows - 1) ) && 1 row header
"Total Pages : "+Padr(lnNeededSheets,3," ")
.Visible = .T.
With .ActiveWorkBook
lnCurrentSheetCount = .sheets.Count
If lnNeededSheets > lnCurrentSheetCount
.sheets.Add(,.sheets(lnCurrentSheetCount),;
lnNeededSheets - lnCurrentSheetCount) && Add new sheets after old ones
Endif
For ix = 1 To m.lnNeededSheets
With .sheets.Item(ix)
.Name = "Page "+Padl(ix,3,"0")
.Activate
lcTempFile = Sys(2015)+".txt"
Copy Next m.lnMaxRows-1 To (m.lcTempFile) Delimited With Tab
_Cliptext = m.lcHeader + Filetostr(m.lcTempFile)
Erase (lcTempFile)
.Range("A1").PasteSpecial()
IF !EOF()
SKIP
endif
"Page no : "+;
padl(m.ix,3)+;
"/"+;
padr(m.lnNeededSheets,3)+ ;
" Elapsed : ",Seconds() - Start
Endwith
Endfor
.sheets.Item(1).Activate
Endwith
Endwith
"Total Elapsed : ",Seconds() - Start

Slow but not bad:

Clear
* Create a test cursor
Create Cursor testcursor (Rcno i,cType c(10), nType i, dType d)
Rand(-1)
For ix = 1 To 200000 && Create 200000 recs cursor
Insert Into testcursor Values ;
(Recco()+1,Sys(2015), Int(Rand()*1000), Date()-Int(Rand()*100))
Endfor
Locate

Start = Seconds()
lnRowsPerCopy = Floor(65000/Fcount())
Dimension aHeader[1,FCOUNT()]
For ix = 1 To Fcount()
aHeader[1,m.ix] = Proper(Field(m.ix))
Endfor

Wait Window Nowait "Pls wait sending data to Excel..."
oExcel = Createobject("Excel.application")
With oExcel
.workbooks.Add && Add a new workbook
.Visible = .T.
lnMaxRows = .ActiveWorkBook.ActiveSheet.Rows.Count && Get max row count
lnNeededSheets = Ceiling( Reccount() / (m.lnMaxRows - 1) ) && 1 row header

"Total Pages : "+Padr(lnNeededSheets,3)
With .ActiveWorkBook
If m.lnNeededSheets > .sheets.Count
.sheets.Add(,.sheets(.sheets.Count),;
m.lnNeededSheets - .sheets.Count) && Add new sheets after old ones
Endif
For ix =1 To m.lnNeededSheets
.WorkSheets(m.ix).Name = "Page "+Padl(m.ix,3,"0")
Endfor
For ix=1 To m.lnNeededSheets
With .WorkSheets(m.ix)
.Activate
.Range(.cells(1,1), .cells(1,Fcount())).Value = GetArrayRef('aHeader')
Endwith

For jx = 1 To Ceiling((m.lnMaxRows - 1) / m.lnRowsPerCopy)
lnRowStart = Recno() - (m.ix - 1) * (m.lnMaxRows - 1) + 1
lnRowEnd = Min(m.lnRowStart + m.lnRowsPerCopy-1, m.lnMaxRows)

Dimension aTrans[m.lnRowEnd - m.lnRowStart + 1,FCOUNT()]
Copy Next (m.lnRowEnd - m.lnRowStart + 1) To Array aTrans
lnCopied = _Tally
Dimension aTrans[m.lnCopied,FCOUNT()]
With .WorkSheets(m.ix)
.Range(.cells(m.lnRowStart,1), .cells(m.lnRowStart + m.lnCopied-1,Fcount())).Value = GetArrayRef('aTrans')
Endwith

If !Eof()
Skip
Else
Exit
Endif
Endfor
"Page no : "+;
padl(m.ix,3)+;
"/"+;
padr(m.lnNeededSheets,3)+ ;
" Elapsed : ",Seconds() - Start
Endfor
Endwith
.sheets.Item(1).Activate
Endwith
"Total Elapsed : ",Seconds() - Start

Function GetArrayRef(tcArrayName)
Return @&tcArrayName

One of the fastest (in multiple workbooks, visually arranged):

Clear all
* Create a test cursor
Create cursor testcursor (Rcno i,cType c(10), nType i, dType d)
Rand(-1)
For ix = 1 to 200000 && Create 200000 recs cursor
Insert into testcursor values ;
(recco()+1,sys(2015), int(rand()*1000), date()-int(rand()*100))
Endfor

Set sysformats on
Set safety off

lnTotal = reccount()
Wait window nowait "Pls wait sending data to Excel..."
Start = seconds()
oExcel = createobject("Excel.application")
#Define xlNormal -4143
With oExcel
.workbooks.add && Add a new workbook
lnMaxRows = .ActiveWorkBook.ActiveSheet.Rows.Count && Get max row count
.ActiveWorkBook.Close(.f.) && Close no save
lnNeededBooks = ceiling( lnTotal / (lnMaxRows - 1) ) && 1 row header
"Total Books : "+padr(lnNeededBooks,3," ")
For ix = lnNeededBooks to 1 step - 1
&& Do a reverse pass so we wouldn't need an extra command to arrange windows
lnStart = ( ix - 1 ) * (lnMaxRows-1) + 1
Copy to ("temp"+padl(ix,2,"0")) ;
for between(recno(),lnStart,lnStart+lnMaxRows-2) ;
type fox2x
.workbooks.Open(sys(5)+curdir()+"temp"+padl(ix,2,"0"))
.ActiveWindow.WindowState = xlNormal
"Book no : "+;
padl(ix,3," ")+;
"/"+;
padr(lnNeededBooks,3," ")+ ;
" Elapsed : ",seconds() - start
Endfor
#Define xlTiled 1
.Windows.Arrange(xlTiled)
.visible = .t.
Endwith
"Total Elapsed : ",seconds() - start