Hi
I looking for vba code import table excel to mysql
Hi
I looking for vba code import table excel to mysql
PLS DO NOT PM; OPEN A THREAD INSTEAD!!!
1) Posting Code
[CODE]PasteYourCodeHere[/CODE]
(or paste your code, select it, click # button)
2) Uploading File(s)
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.
3) Testing the Codes
always back up your files before testing the codes.
4) Marking the Thread as Solved
from Thread Tools (on the top right corner, above the first message)
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.
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.
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
3. Change Sheet1 if needed.
error message
subscript out of range
For the third time, you MUST change your sheet name.
Change:
toWith Worksheets("Sheet1")
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?
I've looked at your workbook and there is no sheet named "my worksheet"height = Worksheets("myworksheet").UsedRange.Rows.Count
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
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 referenceMicrosoft ActiveX Data Objects 2.8 Library?
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
Impossible to export Excel sheet in database mysql by vba code ?
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