I need to import data from Excel to SQL Server using VBA
13,744
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
Author by
Admin
Updated on June 04, 2022Comments
-
Admin almost 2 years
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) & "); "