vba code import table excel to mysql

[etheer]

Hi
I looking for vba code import table excel to mysql

[mancubus]

hi.

see if one of these helps...

http://www.vbaexpress.com/forum/showthread.php?t=8361
http://www.connectionstrings.com/que...l-db-with-vba/
http://www.ozgrid.com/forum/showthread.php?t=21829

[etheer]

do not work all code

i need export all table sheet to datebase mysql using odbc by vba code

[Kenneth Hobs]

Post your code and we can help. The 2nd link provided should be enough to get you going. It is not formatted properly but if you copy the code above that code box and the code in the code box, you will be close to a solution for one sheet. Obviously, you must change the sheet name and other connections details.

Once you get it to work for one sheet, we can show you how to iterate all sheets. Keep in mind that databases are structured. Excel data is not always in nice structure ready for exporting. Short and simple files help us help you.

[etheer]

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"

This code i need fix it

[Kenneth Hobs]

When posting code, be sure to post between code tags. You can click the # icon to insert codes. Most forums work like this.

I modified that code to fix some problems that might arise. Even so, I have not tested it as I usually do as I don't have a MySQL database setup.

Be sure to:
1. Add the ADO object as I commented.
2. Change the username, password, and database connection values.
3. Change Sheet1 if needed.

Sub InsertIntoMySQL()
  Dim height As Long
  Dim rowtable As Long
  Dim strSQL As String
  ' How to add the ADO object:
  ' Tools > References > Microsoft ActiveX Data Objects 2.8 Library
  Dim oConn As ADODB.Connection
  Dim rs As ADODB.Recordset
  
  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
  height = Worksheets("myworksheet").UsedRange.Rows.Count

'insert data into SQL table
  With Worksheets("Sheet1")
    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

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

[etheer]

Thank you so much Kenneth Hobs

I got an error message

subscript out of range

[Kenneth Hobs]

3. Change Sheet1 if needed.

[etheer]

error message

subscript out of range

[Kenneth Hobs]

For the third time, you MUST change your sheet name.
Change:

With Worksheets("Sheet1")

to

With Worksheets("A1")

[etheer]

Still the problem error message subscript out of range

You can fix Book15.xlsm and upload it

[Kenneth Hobs]

Did you replace the sheetname as I explained? I can only do so much for you as I don't have your MySQL database.

Use F8 to debug one line at a time in the Visual Basic Editor (VBE). Hover your cursor over variables to see what they resolve to. Post what line of code causes problems.

When posting code with username or passwords, be sure obfuscate your data.

[Aussiebear]

Does your code error out on this line?

height = Worksheets("myworksheet").UsedRange.Rows.Count

I've looked at your workbook and there is no sheet named "my worksheet"

[etheer]

I edit this code

height = Worksheets("myworksheet").UsedRange.Rows.Count

To
height = Worksheets("M1").UsedRange.Rows.Count

I got error message

ODBC driver does not support the required properties

[Kenneth Hobs]

Is your computer 64 bit? If so, see this: http://www.connectionstrings.com/mys...ctor-odbc-5-1/

I suggest that you insert the code into a Module object, not the Sheet object.

[Aussiebear]

Have you added the ADO reference

Microsoft ActiveX Data Objects 2.8 Library?

[etheer]

Have you added the ADO reference

Microsoft ActiveX Data Objects 2.8 Library?

Yes i add Microsoft ActiveX Data Objects 2.8 Library

[etheer]

Impossible to export Excel sheet in database mysql by vba code ?

[Kenneth Hobs]

Is your computer 64 bit?

[etheer]

32bit

windows 7 ultimate service pack 1 86 x

microsoft office 2010

i can export access to mysql by odbc 5.1

kenneth hobs please you test code on website

http://www.db4free.net