Slow exporting To Excel

[Cris]

I am sending information from access to excel creating a workbook in VBA, and sending the data from a recordset but it takes to much time creating my Excel File.

Someone have an idea about how to optmize my process??

 

[DCrake]

It all depends on how you are sending the data to Excel.

What method are you using? vba/macro
Where are the destination files? Local/Server


David

 

[Guus2005]

Like DCrake said, perhaps the VBA code is not the bottleneck but the network is.

I used to export a recordset line by line but there is a faster way:

Public Function CreateSpreadsheetFromRSFaster(wsExcel As Worksheet, _
                                                  rst As DAO.Recordset, _
                                  Optional blnAutofit As Boolean = True, _
                                   Optional blnHeader As Boolean = True) As Boolean
    Dim iCols As Integer
    
    'Assume success
    CreateSpreadsheetFromRSFaster = True
    
    On Error GoTo Err_CreateSpreadsheetFromRSFaster
    
    If blnHeader Then
        For iCols = 0 To rst.Fields.Count - 1
            wsExcel.Cells(1, iCols + 1).Value = rst.Fields(iCols).Name
        Next
        wsExcel.Range("A2").CopyFromRecordset rst
    Else
        wsExcel.Range("A1").CopyFromRecordset rst
    End If
    
    If blnAutofit Then
        wsExcel.Columns.AutoFit
        wsExcel.Rows.AutoFit
    End If
    
Exit_CreateSpreadsheetFromRSFaster:
    Exit Function

Err_CreateSpreadsheetFromRSFaster:
    Select Case Err
    Case -2147467259
    Case Else
'        ErrorProc Err, Error$, "CreateSpreadsheetFromRSFaster", "modReport"
' Create your own custom error message
    End Select
    CreateSpreadsheetFromRSFaster = False
    Resume Exit_CreateSpreadsheetFromRSFaster

End Function

Enjoy!