{Resolved} Fastest VB way to load Excel from .mdb

[Bruce Fox]

Hey Guys,

I have the following routine to load Table data (of an .mdb) into a pre-defined Excel template.

However, it is VERY slow, is there a quicker way?

Do While Not rst.EOF
 
        lngRowCntr = lngRowCntr + 1
 
        With objExcel
            .range("A" & lngRowCntr) = Format(rst.Fields("Date").Value & "", "dd/mmm/yyyy")
            .range("B" & lngRowCntr) = rst.Fields("Type").Value & ""
            .range("C" & lngRowCntr) = rst.Fields("Registration").Value & ""
            .range("D" & lngRowCntr) = rst.Fields("PIC").Value & ""
            .range("E" & lngRowCntr) = rst.Fields("Crew").Value & ""
            .range("F" & lngRowCntr) = rst.Fields("Details").Value & ""
            .range("G" & lngRowCntr) = rst.Fields("Landings").Value & ""
            .range("H" & lngRowCntr) = rst.Fields("SE ICUS Day").Value & ""
            .range("I" & lngRowCntr) = rst.Fields("SE ICUS Night").Value & ""
            .range("J" & lngRowCntr) = rst.Fields("SE Dual Day").Value & ""
            .range("K" & lngRowCntr) = rst.Fields("SE Dual Night").Value & ""
            .range("L" & lngRowCntr) = rst.Fields("SE Command Day").Value & ""
            .range("M" & lngRowCntr) = rst.Fields("SE Command Night").Value & ""
            .range("N" & lngRowCntr) = rst.Fields("ME ICUS Day").Value & ""
            .range("O" & lngRowCntr) = rst.Fields("ME ICUS Night").Value & ""
            .range("P" & lngRowCntr) = rst.Fields("ME Dual Day").Value & ""
            .range("Q" & lngRowCntr) = rst.Fields("ME Dual Night").Value & ""
            .range("R" & lngRowCntr) = rst.Fields("ME Command Day").Value & ""
            .range("S" & lngRowCntr) = rst.Fields("ME Command Night").Value & ""
            .range("T" & lngRowCntr) = rst.Fields("ME Co-Pilot Day").Value & ""
            .range("U" & lngRowCntr) = rst.Fields("ME Co-Pilot Night").Value & ""
            .range("V" & lngRowCntr) = rst.Fields("Instrument Flight").Value & ""
            .range("W" & lngRowCntr) = rst.Fields("Instrument Ground").Value & ""
        End With
 
        rst.MoveNext
 
    Loop

Note: The Excel Workbook is allready set up and pre-saved (lotsa reasons - multi page and the like)so I cant simply dump the Table into Excel.




Cheers,
Bruce.