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

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?

VB Code:

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.

Have you tried the Cells method of the Range object? This would avoid the conversion of lngRowCntr into a string and the concatenation of the two strings when specifying the cell.

I have not tested the code and have no idea if it would be faster.

VB Code:

Do While Not rst.EOF
 
        lngRowCntr = lngRowCntr + 1
 
        With objExcel.Range
             .Cells(1,lngRowcntr) = Format(rst.Fields("Date").Value & "", "dd/mmm/yyyy")
 
            .Cells(2,lngRowCntr) = rst.Fields("Type").Value & ""
            '...
        End With
 
        rst.MoveNext
 
    Loop

I wonder if a Paste operation would be faster. A GetRows on the Recordset will load everything into an array. Copy the array to the Clipboard and then paste it into the Excel spreadsheet.

Thanks for the reply Bruce.

I'll give those methods a go :)




Bruce.

Copying the RecordSet to the Clipboard, and manualy pasting in Excel is super quick:)


However, when I automate the 'paste', I get "Out of Memory"!

VB Code:

Clipboard.Clear
Clipboard.SetText rst.GetString
objExcel.Range("A2").Select
objExcel.Range("A2") = Clipboard.GetText

(Note: I can Ctl+V when "A2" is selected sucessfully in Excel)

Hmmmm.



Bruce.

Ok, the problem is Clipboard.GetText fails with large data (Ctl+V still works tho in Excel).

I guess I now need to try using SendMessage to Paste....


Edit: Actually, PasteSpecial method may work, now to get home...


Bruce.

The PasteSpecial method is the fast; very fast (I tested with 16,000):

VB Code:

With objExcel
        .Range("A2").Select
        .Range("A2").PasteSpecial
        .Range("$X:$X").Clear   'Clear out the (unwanted - hidden) Tables AutoNumbering
        .Visible = True
        .ActiveWindow.SelectedSheets.PrintPreview   'Show once completed loading data
    End With

Also, for others here is an alternative (using CopyFromRecordset (Ecell 2000+).




Bruce