我需要使用VBA将数据从Excel插入SQL Server [英] I need to insert data into SQL server from Excel using VBA

问题描述

我需要将 test-vba.xlsx 数据插入到SQL Server的特定数据库中

I need to insert test-vba.xlsx data into SQL server to the particular database

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

    Set wkb = Workbooks("test-vba.xlsx").Worksheets("Sheet1").Activate
    sConnString = "Provider=SQLOLEDB;Data Source=PRATEEP-PC\SQLEXPRESS;" & _
                  "Initial Catalog=PPDS_07Dec_V1_Decomposition;" & _
                  "Integrated Security=SSPI;"
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    conn.Open sConnString

    For m = 0 To nrows - 1

推荐答案

以下是一些想法.

Sub UpdateTable()

    Dim cnn As Object
    Dim wbkOpen As Workbook
    Dim objfl As Variant
    Dim rngName As Range
    Workbooks.Open "C:\Users\Excel\Desktop\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=[Database_Name].[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=Database_Name;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

这是另一个想法.

Sub Button_Click()
'TRUSTED CONNECTION
    On Error GoTo errH

    Dim con As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim strPath As String
    Dim intImportRow As Integer
    Dim strFirstName, strLastName As String

    Dim server, username, password, table, database As String


    With Sheets("Sheet1")

            server = .TextBox1.Text
            table = .TextBox4.Text
            database = .TextBox5.Text


            If con.State <> 1 Then

                con.Open "Provider=SQLOLEDB;Data Source=" & server & ";Initial Catalog=" & database & ";Integrated Security=SSPI;"
                'con.Open

            End If
            'this is the TRUSTED connection string

            Set rs.ActiveConnection = con

            'delete all records first if checkbox checked
            If .CheckBox1 Then
                con.Execute "delete from tbl_demo"
            End If

            'set first row with records to import
            'you could also just loop thru a range if you want.
            intImportRow = 10

            Do Until .Cells(intImportRow, 1) = ""
                strFirstName = .Cells(intImportRow, 1)
                strLastName = .Cells(intImportRow, 2)

                'insert row into database
                con.Execute "insert into tbl_demo (firstname, lastname) values ('" & strFirstName & "', '" & strLastName & "')"

                intImportRow = intImportRow + 1
            Loop

            MsgBox "Done importing", vbInformation

            con.Close
            Set con = Nothing

    End With

Exit Sub

errH:
    MsgBox Err.Description
End Sub

另外，请查看下面的链接.

Also, check out the links below.

http://www.cnblogs.com/anorthwolf/archive/2012/04/25/2470250.html

http://www.excel-sql-server.com/excel-sql-server-import-export-using-vba.htm

这篇关于我需要使用VBA将数据从Excel插入SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！