Exports/Outputs Suddenly Very Slow

[rachelkm2]

Hi,

I have an Access 2010 database which has very suddenly become very slow when either exporting to excel or outputting reports. The two common places where this happens are below (this code hasn't been changed any time recently). Any thoughts would be much appreciated!

Example 1 - Printing labels to pdf:

DoCmd.OutputTo acOutputReport, gStrReportName, acFormatPDF, strPathAndFile

Example 2 - Exporting to excel:

Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection
Dim rstExport As New ADODB.Recordset
rstExport.ActiveConnection = cnn1
Dim rstSQL As String
Dim FilterName As String

Dim appExcel As excel.Application
Dim bks As excel.Workbooks
Dim wkb As excel.Workbook
Dim wks As excel.Worksheet
Dim rng As excel.Range

Dim i As Integer
Dim iNumCols As Integer

'Create a new instance of Excel
Set appExcel = CreateObject("Excel.Application")
appExcel.Application.Visible = False
Set bks = appExcel.Workbooks
Set wkb = bks.Add
Set wks = wkb.Sheets(1)

rstSQL = gStrReportQuery
rstExport.Open rstSQL

    wks.Range("A2").CopyFromRecordset rstExport

Set rng = wks.UsedRange

iNumCols = rstExport.Fields.Count
For i = 1 To iNumCols
wks.Cells(1, i).Value = rstExport.Fields(i - 1).Name
Next

With wks.Range("a1").Resize(1, iNumCols)
.Font.Bold = True
.EntireColumn.AutoFit
End With

    With wks
        .Cells.EntireColumn.AutoFit
        .PageSetup.PrintTitleRows = "A1"
    End With

'Add a border to all the cells with data
With wks.UsedRange.Borders
    .LineStyle = 1
    .Weight = xlThin
    .ColorIndex = -4105
End With

'Open the worksheet
    appExcel.Application.Visible = True

appExcel.Application.ScreenUpdating = True

Exit_Sub:
    Set rng = Nothing
    Set wks = Nothing
    Set wkb = Nothing
    Set bks = Nothing
    Set appExcel = Nothing

Any thoughts?

 

[vbaInet]

If it has become slow then you obviously are outputting more records than before. Your code is using CopyFromRecordset which is one of the fastest ways of exporting to Excel so I don't see any problems there. The other method you can try from Access VBA is TransferSpreadsheet.

How many fields and records are you exporting?

 

[rachelkm2]

I don't have any more records than previously. It's just as slow if my list has 12 records or 12,000 (and anywhere from 2 to 20 fields).

 

[boblarson]

Get rid of part of the code. It is not necessary and could be why it is slowing down.

Change this:

'Create a new instance of Excel
Set appExcel = CreateObject("Excel.Application")
appExcel.Application.Visible = False
[B][COLOR=red]Set bks = appExcel.Workbooks
[/COLOR][/B]Set wkb = bks.Add
Set wks = wkb.Sheets(1)

To this:

'Create a new instance of Excel
Set appExcel = CreateObject("Excel.Application")
appExcel.Application.Visible = False

Set wkb = appExcel.WorkBooks.Add
Set wks = wkb.Sheets(1)

Also, you don't need the Application part in this code:
'Open the worksheet
appExcel.Application.Visible = True

Just use

appExcel.Visible = True

And also this part:

Exit_Sub:
    Set rng = Nothing
    Set wks = Nothing
    Set wkb = Nothing
    Set bks = Nothing
    Set appExcel = Nothing

You shouldn't be trying to set appExcel to nothing until you release it.

appExcel.UserControl = True

 

[rachelkm2]

Thanks. I made all of the changes you suggested. I'm still having problems, but I've narrowed it down to the following two sections (specific lines in red):

With wks.Range("A1").Resize(1, iNumCols)
[COLOR="red"].Font.Bold = True
[/COLOR].EntireColumn.AutoFit
End With

And

With wks
.Cells.EntireColumn.AutoFit
[COLOR="red"].PageSetup.PrintTitleRows = "A1"[/COLOR]
End With

Any thoughts?

 

[vbaInet]

The process itself can't be slow in this case. Are you linked to a database server?