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

0
edit delete
are you sure?
asked Paolo Cesari

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

0
edit delete
are you sure?
answered Paolo Cesari

Post an answer but please log in first

Post Answer
  1. Please log in to post answer

Similar Questions

Tagsfor this question

Forumall questions