Insert Record from Excel to MySql using VBA

[ahmed_one]

Hi To All,

For one of my project I need to create a VBA module for export all data in active workbook's active sheet to MySql database table. At present I am using following code which is successfully doing as required:



Public Sub TransMySql()
Height = Application.ActiveSheet.UsedRange.Rows.Count
Dim ws As Worksheet
Set ws = Application.ActiveWorkbook.ActiveSheet
ConnectDB
'insert data into SQL table
With ws
Dim rowtable As Integer
Dim strSQL As String
For rowtable = 2 To Height
strSQL = "INSERT INTO fundmang (fdId, fdName, fdOne, fdTwo, fdThree, Remarks) " & _
"VALUES ('" & esc(Trim(.Cells(rowtable, 1).Value)) & "', '" & _
esc(Trim(.Cells(rowtable, 2).Value)) & "', '" & _
esc(Trim(.Cells(rowtable, 3).Value)) & "', '" & _
esc(Trim(.Cells(rowtable, 4).Value)) & "', '" & _
esc(Trim(.Cells(rowtable, 5).Value)) & "', '" & _
esc(Trim(.Cells(rowtable, 6).Value)) & "')"
rs.Open strSQL, cn
Next rowtable
End With

End Sub


Function esc(txt As String)

esc = Trim(Replace(txt, "'", "\'"))

End Function

Private Sub ConnectDB(xl As String)

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")


cn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & _
"SERVER=localhost;" & _
"DATABASE=fdfund;" & _
"USER=root;" & _
"PASSWORD=;" & _
"Option=3"


End Sub



What I need is to use one statement instead of loop through each row of Excel sheet and send INSERT statement with values from each row, example:



sSql = "INSERT INTO fundmang (fdId, fdName, fdOne, fdTwo, fdThree, Remarks) "
sSql = sSql & "SELECT * FROM [Excel file/sheet I DON'T KNOW WHAT TO PUT HERE]"



Or something like that...

fundmang is the name of Table in MySql database (fdFund), field of which are same as Excel sheet Columns.

Actually I am already use this kind of INSERT with SELECT for mass insert rows from Excel to MS.Access as follows:



sSql = "INSERT INTO fdFolio ([fdName], [fdOne], [fdTwo], [fdThree], [Remarks]) "
sSql = sSql & "SELECT [fd Name] as fdName, [fd Two] as fdTwo, [fd One] as fdOne, [fd Three] as fdThree, Remarks FROM [Excel 8.0;HDR=YES;DATABASE=" & dbwb & "]." & dsh


But this is not supported in MySql as it always gives error, clearly it cannot find the datasource to execute Insert command.

Any ideas/suggestions are highly appreciated.

regards

Ahmed