Information générale
Catégorie:
Codage, syntaxe et commandes
Titre:
Advice on speeding up sending data to Excel
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.
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement