Man kann die Daten aus einer MySQL-Datenbank direkt in Excel einlesen, ohne eine ODBC-Verbindung in der Systemsteuerung definieren zu müssen.

MySQL-Treiber installieren

Man braucht einen ODBC connector, als “Datenbank-Treiber”.

Bei mir läuft Windows 8 64 bit. Daher hatte ich zuerst den 64-Bit Treiber installiert.

Später habe ich herausgefunden, dass die benötigte Variante abhängig von der Excel-Version ist.

Da mein Excel eine 32-bit Version ist, musste ich den 32-bit connector installieren:

http://dev.mysql.com/downloads/connector/odbc/

Excel-Datei

avrio-excel

Excel VBA

[codesyntax lang=”vb”]

Option Explicit

' Module-level constant
Public dbsource As String
Public dblocation As String
Public dbuser As String
Public dbpassword As String
Public dbname As String
Public dbtable As String
Public dbfields As String

Public Sub LoadButton_Click_10()
    InitDbParameter
    LoadButton "10"
End Sub

Public Sub LoadButton_Click_Hour()
    InitDbParameter
    LoadButton "hour"
End Sub

Public Sub LoadButton_Click_Day()
    InitDbParameter
    LoadButton "day"
End Sub

Public Sub LoadButton_Click_Week()
    InitDbParameter
    LoadButton "week"
End Sub

Public Sub InitDbParameter()
    ' Get Parameters from Excel-Sheet
    dbsource = Range("dbSource").Value
    dblocation = Range("dbLocation").Value
    dbuser = TasksSheet.UserInput.Value
    dbpassword = TasksSheet.PasswordInput.Value
    dbfields = Range("dbFields").Value
    dbname = Range("dbname").Value
    dbtable = Range("dbtable").Value
End Sub

Private Function OpenConnection() As ADODB.connection

    Dim connectionString As String
    connectionString = "Driver={MySQL ODBC 5.2 ANSI Driver};Server=" & dblocation & ";Database=" & dbname & ";UID=" & dbuser & ";PWD=" & dbpassword & ";OPTION=3"

    ' Create and open a new connection to the selected source
    Set OpenConnection = New ADODB.connection
    ' MsgBox connectionString
    Call OpenConnection.Open(connectionString)
    ' MsgBox "db opened"

End Function

Public Sub LoadButton(queryType As String)

    Dim dbFieldNo As Long
    Dim sqlSelect As String

    If queryType = "hour" Then
        sqlSelect = "SELECT " & dbfields & " FROM " & dbtable & " WHERE dattim >= Date_Sub(CURRENT_TIMESTAMP(), Interval 1 Hour) ORDER BY dattim ASC "
    ElseIf queryType = "day" Then
        sqlSelect = "SELECT " & dbfields & " FROM " & dbtable & " WHERE dattim >= Date_Sub(CURRENT_TIMESTAMP(), Interval 1 DAY) ORDER BY dattim ASC "
    ElseIf queryType = "week" Then
        sqlSelect = "SELECT " & dbfields & " FROM " & dbtable & " WHERE dattim >= Date_Sub(CURRENT_TIMESTAMP(), Interval 7 DAY) GROUP BY YEAR(dattim),MONTH(dattim),DAY(dattim),HOUR(dattim) "
    Else
        sqlSelect = "SELECT " & dbfields & " FROM " & dbtable & " ORDER BY dattim DESC LIMIT 10"
    End If
    ' sqlSelect = "SELECT id, dattim, Aussen, Wintergarten, Zimmer, Terrasse, Pool, WW_Speicher, Vorlauf, Ruecklauf FROM avrdat LIMIT 10"
    ' sqlSelect = "SELECT * FROM avrdat LIMIT 10"

    Dim output As Range
    Dim rgCols, rgRows As Long
    rgCols = 10
    rgRows = 1000
    Set output = Range("Output").Offset(1).Resize(rgRows, rgCols)

    output.ClearContents

    Dim connection As connection
    Set connection = OpenConnection()

    'MsgBox "Execute sql >" & sqlSelect
    Dim result As ADODB.Recordset
    ' Load all the tasks from the database
    Set result = connection.Execute(sqlSelect)

    ' Insert them into the dedicated area
    Call output.CopyFromRecordset(result)

    ' Add filters
    Range("Output").Worksheet.AutoFilterMode = False
    Range("Output").AutoFilter

    connection.Close
    'MsgBox "Finished - data downloaded to Excel"
End Sub

[/codesyntax]