vba code import table excel to mysql

Hi
I looking for vba code import table excel to mysql

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

do not work all code

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

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.

Code:

---

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

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.

Code:

---

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

---

Thank you so much Kenneth Hobs

I got an error message

subscript out of range

Quote:

---

3. Change Sheet1 if needed.

---

error message

subscript out of range

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

Code:

---

With Worksheets("Sheet1")

---

to

Code:

---

With Worksheets("A1")

---

Still the problem error message subscript out of range

You can fix Book15.xlsm and upload it

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.

Does your code error out on this line?

Code:

---

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

---

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

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

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.

Have you added the ADO reference

Microsoft ActiveX Data Objects 2.8 Library?

Quote:

---

Originally Posted by Aussiebear

Have you added the ADO reference

Microsoft ActiveX Data Objects 2.8 Library?

---

Yes i add Microsoft ActiveX Data Objects 2.8 Library

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

Quote:

---

Is your computer 64 bit?

---

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

That requires some other work to make that connection.

When I get time, I will run a test on my localhost MySQL at home. My work day will be long tomorrow so it may be two days before I get back to this. I work with 64 bit at home so I may have to play with the connection string.

When learning a method, try to keep it simple. Try working with the Sakila database and the Actor table. Make a short 2 or 3 rows in Excel to insert into Actor.

If you installed the workbench, maybe you installed MySQL for Excel? It is in the Data ribbon when installed. Manually using it appends Excel data to your MySQL quickly.
http://dev.mysql.com/doc/mysql-for-excel/en/

:banghead::banghead::banghead:

:cry:

Please help me

First off, you need to make sure that you have the MySQL ODBC driver installed. I have a macro that can show you but you can check it manually in the registry. Look for the name in the registry key, ODBC Drivers. On the computer that I tested this on, notice the driver name.

You need to install the Sakila database for this example. Change the password, ken, to yours. I did not try the Unicode ODBC driver. I just used ANSI for this example. Actors has 200 records. I changed the height to 3 so rows 2 to 3 or 2 rows/records were inserted into Sakila's table Actor. After running this, Actors will have 202 records.

I commented a link the shows a problem using Insert with Open. I just used Execute. The links shows another way to do it.

It took awhile to get MySQL installed and verify that the proper drivers were installed as well. Now you have a 100% tested solution as I would normally post.

Code:

---

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
  ' http://www.connectionstrings.com/mysql-connector-odbc-5-1/
  ' ODBC Drivers: http://dev.mysql.com/downloads/connector/odbc/
  oConn.Open "DRIVER={MySQL ODBC 5.2 ANSI Driver};" & _
    "SERVER=localhost;" & _
    "DATABASE=Sakila;" & _
    "USER=root;" & _
    "PASSWORD=ken;" & _
    "Option=3"

  'number of rows with records
  height = Worksheets("Sheet1").UsedRange.Rows.Count
  height = 3

'insert data into SQL table
  With Worksheets("Sheet1")
    For rowtable = 2 To height
        strSQL = "INSERT INTO actor (actor_id, first_name, last_name) " & _
          "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
          'http://stackoverflow.com/questions/2821917/connecting-to-mysql-from-excel-odbc-driver-does-not-support-the-requested-prope
          oConn.Execute strSQL
    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

---

Error

Attachment 10401

It "errors" because the table Actor does not exist. When you manually look at the table Actor in Workbench, is it there?
Obviously, you are not running the code from a locally installed server.

I tried to setup a web MySQL database at the link db4free.net but was never sent an email detailing that it was setup. I may try again.

Post connection details to your web test Sakila database or PM details to me.

I am running mysqld version 5.6.13.

Thank you so much Kenneth Hobs
Now working code
Attachment 10402

Attachment 10403

But problem repeating rows when update sheet
Attachment 10404

Please help me i need update rows every time run the macro not the write new rows

Attachment 10407

Please mark this thread solved.

I am not sure how you added duplicate records. Actor_id is a primary key for that reason. It should have errored.

Copy the link to this thread and post a new one if you need help using Update in ADO. Most any SQL forum can provide that help. There are several Excel forum posts about ADO with Update that should suffice.

Quote:

---

Originally Posted by etheer

Attachment 10407

---

The problem is repeating rows ever time run the macro

How insert column A To column AJ
cell 1 to 164

Code:

---

'insert data into SQL table
  With Worksheets("M1")
    For rowtable = 2 To height
        strSQL = "INSERT INTO M1 (A, B, C) " & _
          "VALUES ('" & esc(Trim(.Cells(rowtable, 1).Value)) & "', '" & _
          esc(Trim(.Cells(rowtable, 2).Value)) & "', '" & _
          esc(Trim(.Cells(rowtable, 3).Value)) & "')"

---

Add a loop within a loop. The example only has 3 fieldnames/columnnames. You can reference a fieldname by index number to match a column number but I don't recommend that. You can put all fieldnames into an array and iterate that in a loop. I do recommend that method. The index of those array elements should be set the same as the column numbers.

Try this at your own risk (untested).

Code:

---

Sub InsertIntoMySQL2()
  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
  Dim i As Integer, s as string
 
  On Error GoTo ErrHandler
  Set rs = New ADODB.Recordset
  Set oConn = New ADODB.Connection
  ' http://www.connectionstrings.com/mysql-connector-odbc-5-1/
  ' ODBC Drivers: http://dev.mysql.com/downloads/connector/odbc/
  oConn.Open "DRIVER={MySQL ODBC 5.2 ANSI Driver};" & _
    "SERVER=localhost;" & _
    "DATABASE=etheer;" & _
    "USER=root;" & _
    "PASSWORD=1234;" & _
    "Option=3"

  'number of rows with records
  height = Worksheets("M1").UsedRange.Rows.Count
  'height = 3

'insert data into SQL table
  With Worksheets("M1")
    For rowtable = 2 To height
        s = "INSERT INTO M1 " & _
          "VALUES "
        For i = 1 To 35
          strSQL = strSQL & "('" & esc(Trim(.Cells(rowtable, i).Value)) & "',"
        Next i
        strSQL = s & strSQL & "('" & esc(Trim(.Cells(rowtable, 36).Value)) & "')"
        oConn.Execute strSQL
    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

---

Error message

Compile error
Sup or Function not defined

To use the first SQL Insert method, you need to know the fieldnames. That string can be large so it may not be that efficient.

I don't have time to finish this 2nd method right now. It needs the loop to add the Excel rows from Height as records. In the loop, use rs.AddNew, iterate through each fieldname in the recordset, and add field values. I added a loop and debug.print to show you how to do these things. After adding the field values for a new record in the range loop, use rs.Update to update the record set. I will finish this when I get time. This method is similar to what I posted in a recent thread. That user was able to gleam what he needed.

Code:

---

' http://www.utteraccess.com/forum/lofiversion/index.php/t1947720.html

Sub InsertIntoMySQL()
  Dim height As Long
  Dim rowtable As Long, row 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
  Dim col As Integer, wsName As String, dbName As String
  Dim a As Variant
 
  wsName = "Film"
  dbName = "film"
 
  On Error GoTo ErrHandler
  Set rs = New ADODB.RecordSet
  Set oConn = New ADODB.Connection
  oConn.Open "DRIVER={MySQL ODBC 5.2 ANSI Driver};" & _
    "SERVER=localhost;" & _
    "DATABASE=Sakila;" & _
    "USER=root;" & _
    "PASSWORD=ken;" & _
    "Option=3"

  'number of rows with records
  height = Worksheets(wsName).UsedRange.Rows.Count
  'height = 3
   
' Create RecordSet
  Set rs = New ADODB.RecordSet
' Record locking  ' http://www.utteraccess.com/wiki/index.php?title=Beginners_Guide_To_ODBC
  rs.CursorLocation = adUseClient
  rs.CursorType = adOpenStatic
  rs.LockType = adLockOptimistic
   
  With rs
    strSQL = "SELECT * FROM " & dbName
    rs.Open Source:=strSQL, ActiveConnection:=oConn
  '      Write the field names
    For col = 0 To .Fields.Count - 1
      'Debug.Print rs.Fields(col).Name
    Next col
    MsgBox rs.RecordCount
       
'  Write the recordset
    'Range("A1").Offset(1, 0).CopyFromRecordset rs
    .MoveFirst
    a = rs.GetRows
    'MsgBox "First Record's film_id: " & a(0, 0), , "Second Record's film_id: " & a(0, 1)
    'MsgBox "First Record's title: " & a(1, 0), , "Second Record's title: " & a(1, 1)

    If .RecordCount < 1 Then GoTo EndNow
    .MoveFirst
    For row = 0 To 0  '(.RecordCount - 1)
      For col = 0 To .Fields.Count - 1
        Debug.Print "Row: " & row, "Field: " & col, .Fields(col).Name, .Fields(col).Value
      Next col
      .MoveNext
    Next row
  End With
 
ErrHandler:
  If Err.Description <> "" And Err.Source <> "" Then
    MsgBox Err.Description, vbCritical, Err.Source
  End If
 
EndNow:
    Set rs = Nothing
    oConn.Close
    Set oConn = Nothing
End Sub

---

I inserted the first 3 records from sakila.film database in, https://app.box.com/s/dzbthvp7sn50q9eaijaj. I just changed the film_id autoincrement values. If those 3 records can be updated, you will be set. Notice in the example that you can add field values by using the field's name or the 0-based index for the field.

This will then give you two ways to Insert records once I finish the range loop.

Attachment 10429

Records 0

Attachment 10430

when run macro reading database but does not writing

Error message

either bof or eof is true or the current record has been deleted

Quote:

---

It needs the loop to add the Excel rows from Height as records.

---

I do not get any error on multiple runs of Sub InsertIntoMySQL. The other Sub in the other Module was the code to the link that I referenced way back in this thread. The MsgBox shows 1000 records in sakila.film. The Immediate window shows the first records values for each field. From that, you should be able to gleam how to iterate records and fields names and update from there. As I said, when I get time, I will add those few lines of code to do it.

Code:

---

Row: 0        Field: 0      film_id      1
Row: 0        Field: 1      title        ACADEMY DINOSAUR
Row: 0        Field: 2      description  A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies
Row: 0        Field: 3      release_year  2006
Row: 0        Field: 4      language_id    1
Row: 0        Field: 5      original_language_id        Null
Row: 0        Field: 6      rental_duration              6
Row: 0        Field: 7      rental_rate    0.99
Row: 0        Field: 8      length        86
Row: 0        Field: 9      replacement_cost            20.99
Row: 0        Field: 10    rating        PG
Row: 0        Field: 11    special_features            Deleted Scenes,Behind the Scenes
Row: 0        Field: 12    last_update  2/15/2006 5:03:42 AM

---