How to show recordset MySQL data fast(er) in excel?

[Mrslow2013]

Hi There,
Please see below code.
it takes about 0.01 seconds to get data from the MySQl database
It takes a lot longer to fill every single cell with the proper value
does any faster way exist to fill cells with the proper data?
tried playing around with the getstring option as this holds ALL data but couldn't find a proper way to use this and show data properly

  mySQL = ""
    mySQL = mySQL & "SELECT ID, txtJPost, txtVolgnr, txtShipmentID, txtDateInvoice, txtAmountex, txtcur, txtROE, txtPaid"
    mySQL = mySQL & " FROM invoicedb"
    mySQL = mySQL & " WHERE txtShipmentID IN "
    mySQL = mySQL & "(SELECT shipmentID FROM shipmentdb "
    mySQL = mySQL & "WHERE InputDate >= '" & Year(Sheet1.Range("E10").Value) & "-" & Month(Sheet1.Range("E10").Value) & "-"
    mySQL = mySQL & Day(Sheet1.Range("E10").Value) & "'"
    mySQL = mySQL & " AND InputDate <= '" & Year(Sheet1.Range("G10").Value) & "-" & Month(Sheet1.Range("G10").Value) & "-"
    mySQL = mySQL & Day(Sheet1.Range("G10").Value) + 1 & "')"
    mySQL = mySQL & " ORDER BY txtShipmentID"
    'MsgBox mySQL
    myRS.Source = mySQL
    Set myRS.ActiveConnection = myconn
    myRS.CursorLocation = adUseClient
    myRS.Open
    Do While Not myRS.EOF
      Sheet2.Range("A" & Step1).Value = myRS.Fields("txtShipmentID").Value
      Sheet2.Range("B" & Step1).Value = "B"
      Sheet2.Range("C" & Step1).Value = myRS.Fields("ID").Value
      Sheet2.Range("D" & Step1).Value = myRS.Fields("txtDateInvoice").Value
      Sheet2.Range("E" & Step1).Value = myRS.Fields("txtVolgNr").Value
      Sheet2.Range("F" & Step1).Value = Replace(myRS.Fields("txtAmountEX").Value, ",", ".")
      Sheet2.Range("F" & Step1).Value = Sheet2.Range("F" & Step1).Value * -1
      Sheet2.Range("G" & Step1).Value = myRS.Fields("txtROE").Value
      Sheet2.Range("H" & Step1).Value = myRS.Fields("txtPaid").Value
      Sheet2.Range("I" & Step1).Value = myRS.Fields("txtCUR").Value
      Sheet2.Range("J" & Step1).Value = myRS.Fields("txtJPost").Value
      If myRS.Fields("txtCUR").Value <> "EUR" Then
        Sheet2.Range("F" & Step1).Value = myRS.Fields("txtAmountEX").Value * CDec(myRS.Fields("txtROE").Value)
        Sheet2.Range("F" & Step1).Value = Sheet2.Range("F" & Step1).Value * -1
      End If
      myRS.MoveNext
      Step1 = Step1 + 1
    Loop
  myRS.Close
  myconn.Close

Appreciated!

[si_the_geek]

Welcome to VBForums

There are two ways to make that kind of thing faster, the first is to use the CopyFromRecordset method that Excel provides (but you need all of the data in the recordset [in the right positions], rather than using code to generate/re-position values), and the other is to fill an array which you can pass to Excel in one go.

There are examples of both of those methods in my Excel automation tutorial, see the link in my signature.


In your case CopyFromRecordset wouldn't quite work due to column F. The rest could be done by simply changing the first line of the SQL to this:

    mySQL = mySQL & "SELECT txtShipmentID, 'B', ID, txtDateInvoice, txtVolgnr, '',  txtROE, txtPaid, txtcur, txtJPost "

..and adding a CopyFromRecordset call. Then you'd need to do a .MoveFirst on the recordset and loop to set column F appropriately (using the loop you've got now, but only the bits about column F)


On top of that, the code for column F can be made faster by not re-reading data from the sheet unnecessarily:

      If myRS.Fields("txtCUR").Value = "EUR" Then
        Sheet2.Range("F" & Step1).Value = CDec(Replace(myRS.Fields("txtAmountEX").Value, ",", ".")) * -1
      Else
        Sheet2.Range("F" & Step1).Value = myRS.Fields("txtAmountEX").Value * CDec(myRS.Fields("txtROE").Value) * -1
      End If