>In my application I use the following code to create an Excel spreadsheet and populate it with the contents of an array that was created with a SQL Select into statement. Sometimes the array is large, over a 1000 records, and this process can be lengthy (5-10 minutes). Any advice on how to speed this up?
>
>Wait Window "Creating Excel Spreadsheet..." nowait
>tmpSheet = GetObject('','excel.sheet')
>XLApp = tmpsheet.application
>XLApp.WorkBooks.Add()
>XLSheet = XLApp.ActiveSheet
>XLSheet.Cells(1,1).Value = "Soc_Sec_Num"
>XLSheet.Cells(1,2).Value = "Employee_Name"
>XLSheet.Cells(1,3).Value = "Ded_Code"
>XLSheet.Cells(1,4).Value = "ER"
>XLSheet.Cells(1,5).Value = "UDDS"
>XLSheet.Cells(1,6).Value = "Fund"
>XLSheet.Cells(1,7).Value = "Act"
>XLSheet.Cells(1,8).Value = "Acct"
>FOR i = 1 to _tally
> j = i + 1
> XLSheet.Cells(j,1).Value = customized(i,1)
> XLSheet.Cells(j,2).Value = customized(i,2)
> XLSheet.Cells(j,3).Value = customized(i,3)
> XLSheet.Cells(j,4).Value = customized(i,4)
> XLSheet.Cells(j,5).Value = customized(i,5)
> XLSheet.Cells(j,6).Value = customized(i,6)
> XLSheet.Cells(j,7).Value = customized(i,7)
> XLSheet.Cells(j,8).Value = customized(i,8)
>ENDFOR
>XLApp.ActiveWindow.WindowState = 2
>XLSheet.Cells.Select
>XLSheet.Cells.EntireColumn.AutoFit
>XLSheet.Range("A1").Select
>Wait Window "Excel Spreadsheet Complete!" nowait
>RELEASE tmpSheet
>XLApp.Visible = .t.

Hello Jeff,

I have the same problem here in an app where a generic option offers download and/or uploads from Excel.

Fine but as you discovered ... pumping VFP variables into Excel thru
OLE automation is awfull time-consuming.

I tried different solutions but never was able to speed up the process :

XLSheet.Cells(i,j) = aVFPvar

As as i a am concerned the current workaround is :

1- create a temp DBF cursor,
2- COPY TO myworksheet.xls TYPE XL5
3- open the temp.xls within EXCEL
4- do the cosmetics (autofit,font,...)
5- save it

As it is a destructive process that can potentially destroy sophisticated multi-sheets documents i also offer a more time-consuming refresh option that sends Excel tons of :

XLSheet.Cells(i,j) = aVFPvar

If anyone has a better workararound i'd be glad to read. I alas don't think it is possible to "feed" Excel cells ranges with VFP array.

François