I need to import data from Excel to SQL Server using VBA

Question

I am trying to import data from another Excel workbook on my PC into SQL Server. I tried with this code, but it doesn't work:

Sub insertion()
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sConnString As String
    Dim rsstring As String
    Dim m, nrows As Integer
    Dim loRH As Excel.ListObject

    Workbooks("test-vba.xls").Activate

    *connection*
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    conn.Open sConnString
    MsgBox "Start"

    Set loRH = wkb.ListObjects(1)
    loRH.ListColumns(1).DataBodyRange.Select

    MsgBox "Start"
    nrows = WorksheetFunction.CountA(Selection)

    For m = 0 To nrows - 1
        rsstring = "insert into MPN_Materials values(MPN Material, Material 
description, Int. material no.,  MPN,  Manufact.,  Matl Group, Material 
Description, Last Chg., BUn) values" _
        & "("loRH.DataBodyRange.Cells(m + 1, 1) & "', 
        & loRH.DataBodyRange.Cells(m + 1, 2) & ", " _
        & loRH.DataBodyRange.Cells(m + 1, 3) & ", " _
        & loRH.DataBodyRange.Cells(m + 1, 4) & ", " _
        & loRH.DataBodyRange.Cells(m + 1, 5) & ", " _
        & loRH.DataBodyRange.Cells(m + 1, 6) & ", " _
        & loRH.DataBodyRange.Cells(m + 1, 7) & ", " _
        & loRH.DataBodyRange.Cells(m + 1, 8) & ", " _
        & loRH.DataBodyRange.Cells(m + 1, 9) & "); "

Answer 1

What does 'doesn't work' mean? What kind of error do you get? Maybe one of the solutions below will help . . .

Sub UpdateTable()

    Dim cnn As Object
    Dim wbkOpen As Workbook
    Dim objfl As Variant
    Dim rngName As Range
    Workbooks.Open "C:\your_path_here\Excel_to_SQL_Server.xls"
    Set wbkOpen = ActiveWorkbook
    Sheets("Sheet1").Select
    Set rngName = Range(Range("A1"), Range("A1").End(xlToLeft).End(xlDown))
    rngName.Name = "TempRange"
    strFileName = wbkOpen.FullName
    Set cnn = CreateObject("ADODB.Connection")
    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFileName & ";Extended Properties=""Excel 12.0 Xml;HDR=Yes"";"
    nSQL = "INSERT INTO [odbc;Driver={SQL Server};Server=Server_Name;Database=[Your_Database].[dbo].[TBL]]"
    nJOIN = " SELECT * from [TempRange]"
    cnn.Execute nSQL & nJOIN
    MsgBox "Uploaded Successfully"
    wbkOpen.Close
    Set wbkOpen = Nothing

End Sub

Sub InsertInto()

'Declare some variables
Dim cnn As adodb.Connection
Dim cmd As adodb.Command
Dim strSQL As String

'Create a new Connection object
Set cnn = New adodb.Connection

'Set the connection string
cnn.ConnectionString = "Server_Name;Database=Your_Database;Trusted_Connection=True;"

'Create a new Command object
Set cmd = New adodb.Command

'Open the connection
cnn.Open
'Associate the command with the connection
cmd.ActiveConnection = cnn

'Tell the Command we are giving it a bit of SQL to run, not a stored procedure
cmd.CommandType = adCmdText

'Create the SQL
strSQL = "UPDATE TBL SET JOIN_DT = 2013-01-13 WHERE EMPID = 2"

'Pass the SQL to the Command object
cmd.CommandText = strSQL

'Open the Connection to the database
cnn.Open

'Execute the bit of SQL to update the database
cmd.Execute

'Close the connection again
cnn.Close

'Remove the objects
Set cmd = Nothing
Set cnn = Nothing

End Sub

As an alternative, consider one of the links below.

http://www.ozgrid.com/forum/showthread.php?t=169953

http://stackoverflow.com/questions/2567150/excel-vba-sql-data

http://msgroups.net/microsoft.public.excel.programming/vba-to-export-large-tables/61433

http://www.codeproject.com/Questions/475817/Howplustoplusupdateplussqlplusserverplusdataplusfr

http://www.excelguru.ca/forums/showthread.php?992-SQL-Select-Insert-Update-queries-from-Excel-vba

http://www.mrexcel.com/forum/excel-questions/617303-updating-records-access-table-using-excel-visual-basic-applications.html

http://www.excelforum.com/excel-programming-vba-macros/501147-how-to-use-vba-to-update-a-sql-server-table-from-a-spreadsheet.html

Answer 2

Just addendum, ensure to add Tools References from within VBA module for Dim cnn As ADODB.Connection 'msado15.dll attached via Tools | Reference | Microsoft ActiveX Data Objects 6.1 Library or latest version.