General Programming Help
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
 



Go Back   Dev Articles Community ForumsProgrammingGeneral Programming Help

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Display Modes
 
Unread Dev Articles Community Forums Sponsor:
  #1  
Old December 4th, 2013, 01:04 AM
ahmed_one ahmed_one is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Dec 2013
Posts: 1 ahmed_one User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 28 m 10 sec
Reputation Power: 0
Insert Record from Excel to MySql using VBA

Hi To All Experts,

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:

Code:

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:

Code:
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:

Code:
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

Reply With Quote
Reply

Viewing: Dev Articles Community ForumsProgrammingGeneral Programming Help > Insert Record from Excel to MySql using VBA


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.

© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap