D

[anu]

Hi All,
I am trying to export data from Datagrid to Excel. IT parses each row
from Datagrid to Excel.This is the code that i have:

Dim iRowIndex As Integer
Dim iColIndex As Integer
Dim iRecordCount As Integer
Dim iFieldCount As Integer
Dim avRows As Variant
Dim excelVersion As Integer

Screen.MousePointer = vbHourglass

On Error GoTo expError

Adodc1.Recordset.MoveFirst

'--read all records into array
avRows = Adodc1.Recordset.GetRows()

'--Determine how many fields and records
iRecordCount = UBound(avRows, 2) + 1
iFieldCount = UBound(avRows, 1) + 1

'--Create reference variable for the spreadsheet
Set objExcel = GetObject("", "Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add

'--Ensures Excel remains visible if we switch to the active sheet
Set objTemp = objExcel

excelVersion = Val(objExcel.Application.Version)
If (excelVersion >= 8) Then
Set objExcel = objExcel.ActiveSheet
End If



'--add data
With objExcel
For iRowIndex = 2 To iRecordCount + 1
For iColIndex = 1 To iFieldCount
.Cells(iRowIndex - 1, iColIndex).Value = avRows(iColIndex - 1,
iRowIndex - 2)
Next
Next
End With

objExcel.Cells(1, 1).CurrentRegion.EntireColumn.AutoFit
objExcel.Cells(1, 1).CurrentRegion.EntireRow.AutoFit


The problem is there are around 8000 rows in the datagrid so Excel so
it takes approximately 10 min to do it. Is theer a method to copy data
from Datagrid to Excel?? Or any other way which reduces time? Help
Please.
Thanks
anu

 

[Tim]

Transfer the data to an array first, and then to Excel in a single
operation.

Tim