How to show recordset MySQL data fast(er) in excel?
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
Code:
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!
Re: How to show recordset MySQL data fast(er) in excel?
Welcome to VBForums :wave:
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:
Code:
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:
Code:
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
