Consulting

Results 1 to 9 of 9

Thread: Export data from Excel to SQL

  1. #1
    VBAX Mentor anandbohra's Avatar
    Joined
    May 2007
    Location
    Mumbai
    Posts
    313
    Location

    Export data from Excel to SQL

    Hi friends

    as all of us knows that SQL is best for database storage so i want the data to be stored on SQL.

    so i want to have one button in my Excel sheet so that the moment user click the button that data should go to SQL in specified table.

    can anyone knows code for this.
    (I know in sql through a simple 2 line code can do the same but my user dont know SQL i want they only click button in EXCEL & data should go directly to SQL)


    thanks in advance

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,446
    Location
    Sub AddData()
        Dim oConn As Object
        Dim sSQL As String
        Set oConn = CreateObject("ADODB.Connection")
        oConn.Open "Provider=sqloledb;" & _
        "Data Source=myServerName;" & _
        "Initial Catalog=myDatabaseName;" & _
        "User Id=myUsername;" & _
        "Password=myPassword"
        sSQL = "INSERT INTO Contacts (FirstName, LastName,Phone, Notes) " & _
        "        VALUES ('Bob','Phillips','01202 345678','me')"
        oConn.Execute sSQL
        oConn.Close
        Set oConn = Nothing
    End Sub
    Last edited by Aussiebear; 03-03-2025 at 02:50 AM.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Mentor anandbohra's Avatar
    Joined
    May 2007
    Location
    Mumbai
    Posts
    313
    Location
    Thanks XLD for this quick reply
    my data is located in sheet named Backup
    there are 30 column ( column header is named considering SQL requirement)
    rows are nearly 5000.
    so i want all of them should go to SQL at the moment

    what changed have to be made in code given by u ?
    should i name my range before running code ?

    the above question arise coz in given code it wont specify from where to copy & what to copy & paste in SQL

  4. #4
    VBAX Mentor anandbohra's Avatar
    Joined
    May 2007
    Location
    Mumbai
    Posts
    313
    Location
    i also posted this query in http://www.ozgrid.com/forum/showthre...782#post367782

    now i am getting
    ISAM error (Could not find installed ISAM)

  5. #5
    VBAX Mentor anandbohra's Avatar
    Joined
    May 2007
    Location
    Mumbai
    Posts
    313
    Location
    hi all pl solve this simple "for loop"

    Sub testexportsql()
        Dim Cn As ADODB.Connection
        Dim ServerName As String
        Dim DatabaseName As String
        Dim TableName As String
        Dim UserID As String
        Dim Password As String
        Dim rs As ADODB.Recordset
        Dim RowCounter As Long
        Dim NoOfFields As Integer
        Dim StartRow As Long
        Dim EndRow As Long
        Dim ColCounter As Integer
        Set rs = New ADODB.Recordset
        ServerName = "ServerName " ' Enter your server name here
        DatabaseName = "DatabaseName " ' Enter your  database name here
        TableName = "TableName " ' Enter your Table name here
        UserID = "UserID" ' Enter your user ID here
        ' (Leave ID and Password blank if using windows Authentification")
        Password = " Password" ' Enter your password here
        NoOfFields = 20 ' Enter number of fields to update (eg. columns in your worksheet)
        StartRow = 3 ' Enter row in sheet to start reading  records
        EndRow = 6 ' Enter row of last record in sheet
        '  CHANGES
        Dim shtSheetToWork As Worksheet
        Set shtSheetToWork = ActiveWorkbook.Worksheets("SQL")
        Set Cn = New ADODB.Connection
        Cn.Open "Driver={SQL Server};Server=" & ServerName & ";Database=" & DatabaseName & _
        ";Uid=" & UserID & ";Pwd=" & Password & ";"
        rs.Open TableName, Cn, adOpenKeyset, adLockOptimistic
        ' EndRow = shtSheetToWork.Cells(Rows.Count, 1).End(xlUp).Row
        For RowCounter = StartRow To EndRow
            rs.AddNew
            For ColCounter = 1 To NoOfFields
                rs(ColCounter - 1) = shtSheetToWork.Cells(RowCounter, ColCounter)
                ColCounter = ColCounter + 1
            Next ColCounter
        Next RowCounter
        rs.UpdateBatch
        ' Tidy up
        rs.Close
        Set rs = Nothing
        Cn.Close
        Set Cn = Nothing
    End Sub
    if u run this code u will get error on this line


     rs(ColCounter - 1) = shtSheetToWork.Cells(RowCounter, ColCounter)
    pl solve this as this code transfer EXCEL data into SQL
    Last edited by Aussiebear; 03-03-2025 at 02:54 AM.

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,446
    Location
    Maybe ColCounter -1 evaluates to 0 which is an invalid index?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    VBAX Mentor anandbohra's Avatar
    Joined
    May 2007
    Location
    Mumbai
    Posts
    313
    Location
    Thanks XLD for reply
    but i tried that one also
    when i was looking code in break mode i takes value of colcounter as 17 so i think it is not running as per loop means 1 to total column specified by user.

    again when i do some R&D in codes it paste NULL values to some or all fields in SQL which even SQL wont recognize & u have to truncate the table.

    pl refer the attachment & see where it turns into a bug

  8. #8
    Hi friends,

    i want to have one button in my Excel sheet so that the moment when user click the button that data in excel should go to SQL in specified table.

    can anyone knows code for this? Please suggest me.

    Thanks in advance.

  9. #9
    VBAX Newbie
    Joined
    Apr 2017
    Posts
    1
    Location
    Beautifully Executable Code. Amazing.

    Quote Originally Posted by xld View Post
    Sub AddData()
        Dim oConn As Object
        Dim sSQL As String
        Set oConn = CreateObject("ADODB.Connection")
        oConn.Open "Provider=sqloledb;" & _
        "Data Source=myServerName;" & _
        "Initial Catalog=myDatabaseName;" & _
        "User Id=myUsername;" & _
        "Password=myPassword"
        sSQL = "INSERT INTO Contacts (FirstName, LastName,Phone, Notes) " & _
        "        VALUES ('Bob','Phillips','01202 345678','me')"
        oConn.Execute sSQL
        oConn.Close
        Set oConn = Nothing
    End Sub
    Last edited by Aussiebear; 03-03-2025 at 02:55 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •