How to export Excel worksheet to MySQL DB (with VBA)

Hello everyone! I have a big problem :)
How can I export an Excel spreadsheet (only a single worksheet) to a remote MySQL database using VBA code? I have always imported a .csv file with phpmyadmin, but in this case I need to automate all the export process from Excel.
So, first I have to create the DB (if not exists), then a table (if not exists) with some fiels in the first row of the sheet (example: |Code1|Description1|p1|Code2|Description2|p2|) and then with an automated process I have to populate the database table.
I hope that someone can help me!
Thanks,
PAOLO

1 answer

This WORKS FINE! :)

Dim oConn As ADODB.Connection
Dim rs As ADODB.Recordset

'remove dangerous characters
Function esc(txt As String)
esc = Trim(Replace(txt, "'", "'"))
End Function

Private Sub cmdInsertData_Click()
On Error GoTo ErrHandler
Set rs = New ADODB.Recordset
Set oConn = New ADODB.Connection
oConn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & _
"SERVER=myserver;" & _
"DATABASE=mydatabase;" & _
"USER=myuser;" & _
"PASSWORD=mypassword;" & _
"Option=3"

'number of rows with records
Dim height As Integer
height = Worksheets("myworksheet").UsedRange.Rows.Count

'insert data into SQL table
With Worksheets("myworksheet")
    Dim rowtable As Integer
    Dim strSQL As String
    For rowtable = 2 To height
        strSQL = "INSERT INTO mysqltable (column1, column2, column3) " & _
        "VALUES ('" & esc(Trim(.Cells(rowtable, 1).Value)) & "', '" & _
        esc(Trim(.Cells(rowtable, 2).Value)) & "', '" & _
        esc(Trim(.Cells(rowtable, 3).Value)) & "')"
        rs.Open strSQL, oConn, adOpenDynamic, adLockOptimistic
    Next rowtable
End With

MsgBox "Insert with success " & Trim(Str(rowtable - 2)) & " records", vbInformation, "Verification Data Entry"

ErrHandler:
If Err.Description <> "" And Err.Source <> "" Then
MsgBox Err.Description, vbCritical, Err.Source
End If
End Sub