{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.

[brucevde]

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.

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.

[Bruce Fox]

Thanks for the reply Bruce.

I'll give those methods a go




Bruce.

[Bruce Fox]

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"!

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.

[Bruce Fox]

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.

[Bruce Fox]

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

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