Populating Excel with Access records VERY slow 3

[GoDawgs]

It's been a while since I've posted here...but I'm getting frustrated.

I've got a process that dumps a bunch of records for Reps from Access into an Excel file...and in between each Rep does a bunch of formatting (that's the reason for not just dumping the entire recordset).

A section of the code is below, but basically it's cycling through the recordset and pasting each field into the appropriate column before moving on.

The problem is it's literally taking 8 seconds to dump one field into one cell (I've stepped through and watched it), and there are 15 fields to dump per record...and 1,000 plus records. That starts adding up.

I have a completely separate database doing almost exactly the same thing (it's for a different company, so some of the fields are different...but the vba process is the same) and I have no problems...when I step through everything happens instantaneously (as it should).

Anybody seen anything like this? Any tips? Thanks for any help.

Kevin

_____________________________________________________

    Set DB = CurrentDb
    Set QDF = DB.QueryDefs("5_Export")
    QDF.Parameters("EnterOffice") = TheOffice
    Set RST = QDF.OpenRecordset
    
    CurrentRow = SectionRow
    RepStart = SectionRow
    
    With RST
        TheSheet.Cells(CurrentRow, 1) = !CaseID
        TheSheet.Cells(CurrentRow, 2) = !Office
        TheSheet.Cells(CurrentRow, 3) = !AgentName
        TheSheet.Cells(CurrentRow, 4) = !CallDate
        TheSheet.Cells(CurrentRow, 5) = !BTN
        TheSheet.Cells(CurrentRow, 6) = !OrderType
        TheSheet.Cells(CurrentRow, 7) = !BillName
        TheSheet.Cells(CurrentRow, 8) = !Main_Package
        TheSheet.Cells(CurrentRow, 9) = !SBCLD
        TheSheet.Cells(CurrentRow, 10) = !DSL
        TheSheet.Cells(CurrentRow, 11) = !DSLPlan
        TheSheet.Cells(CurrentRow, 12) = !N_ADL1
        TheSheet.Cells(CurrentRow, 13) = !Dish
        TheSheet.Cells(CurrentRow, 14) = !Homezone
        TheSheet.Cells(CurrentRow, 15) = !RepCommPaidAmt
        TheSheet.Cells(CurrentRow, 16) = !CommPlan

 

[PHV]

Why not using the CopyFromRecordset method of the Excel.Range object and bulk formatting after ?

BTW, no event procedures in "TheSheet" ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GoDawgs]

Basically the "CopyFromRecordset" is out because after each rep's section there's about 8 rows of extra data/formatting to be thrown in (a big yellow box with the Rep's total, the Rep's previous balance, etc)...mostly done just to keep things looking the same way they did in the past.

If I can't figure anything out though I'm gonna have to switch it over...2 hours to run through 1,000 records is not quite feasible.

 

[GoDawgs]

Sorry, missed your second comment. That's how I've always dumped data to specific cells...but since I've got nobody to bounce it off of, let me know if I'm missing an easier way.

This is my code for opening the file and setting the sheet:

    Set xlApp = New Excel.Application
    xlApp.Workbooks.Open Filename:=FilePath
    Set xlBook = xlApp.ActiveWorkbook
    
    
    Set TheSheet = xlBook.Worksheets("Summary")
    TheSheet.Activate

 

[PHV]

I rephrase: any VBA code in FilePath ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

Just in case, you may try this before dumping the data:
xlApp.Calculation = -4135 ' -4135=xlCalculationManual

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[GoDawgs]

Nope, just a variable with the name of the Excel file...

    TemplatePath = "\\Templates\AttCommission.xls"
    
    FilePath = "\\Reports\AttCommission-" & FullOffice & "-" & Format(Date, "yymmdd") & ".xls"

    
    FileCopy TemplatePath, FilePath

 

[PHV]

I obviously asked if the workbook (not the variable !) contained some macros.

 

[GoDawgs]

Ha ha...I thought that was a strange question...sorry, it's been a long day.

No, there's nothing in the Excel file itself, it's a brand new file with column headers and nothing else.

I just tried the manual calculation code...didn't change anything. Good idea to have that in there anyway, so thanks.

 

[macropod]

Hi,

Instead of popoulating the cells one at a time, try collecting all the variables into a string, pushing that into a single cell, then using Excel's Text To Columns function to parse the data there. Something like:

With RST
TmpStr = !CaseID & "," & !Office & "," & !AgentName & "," & !CallDate & "," & !BTN & "," & _
    !OrderType & "," & !BillName & "," & !Main_Package & "," & !SBCLD & "," & !DSL & "," & _
    !DSLPlan & "," & !N_ADL1 & "," & !Dish & "," & !Homezone & "," & !RepCommPaidAmt & "," & !CommPlan
    With TheSheet.Cells(CurrentRow, 1)
        .Value = TmpStr
        xlApp.TextToColumns DataType:=xlDelimited, Comma:=True
    End With
End With

This way, you might only cop the 8-second delay per record, rather than per field, plus a trivial overhead for the TextToColumns action.

Cheers

[MS MVP - Word]

 

[HughLerwill]

Can you not dump the whole thing (QueryTable?) and then do the formatting. If you need reference to the related Access record when you format a row in the sheet then you should be able to get it back using its CaseID in the sheet which I guess is (or could be) a key field.

 

[GoDawgs]

Thanks for everyone's help...I ran it this morning with Excel visible so I could see what was going on, and I found the culprit. After each rep I was putting in a page break (these sheets all get printed and sent to the reps)...the code worked instantly until it hit that first page break...then every record after that was ridiculously slow.

I still needed the page breaks, so I just moved that part to the end.