CODEWITHSUNDEEP
sql-servervbaexcel
Aipo
Aipo

Reputation: 1985

How to connect to SQL Server using Excel Macro's?

I have SQLEXPRESS connection details. I want to connect to database using VBA way, and export data from SQL Server table items to new sheet in current Excel workbook and specified sheet.

I have tried data import from excel "External Data", that gives me full table when adding it to excel workbook.

How to add to Excel only 1 or 2 columns from my external table?

Updated: my code right now is

Sub SQL_Connection()

Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim query As String
Set con = New ADODB.Connection
Set rs = New ADODB.Recordset

strCon = "Provider=SQLOLEDB; " & _
    "Data Source="\\localhost\SQLEXPRESS"; " & _
    "Initial Catalog=catalog;" & _
    "User ID=User; Password=password; Trusted_Connection=yes"
con.Open (strCon)
End Sub

Getting compile error:

Syntax error

Upvotes: 2

Views: 9058

Answers (1)

love-to-code
love-to-code

Reputation: 33

You first need to create and open the connection with the database. Here I am posting a sample example for three rows:

Sub Button1_Click()

Dim conn As New ADODB.Connection
Dim iRowNo As Integer
Dim sCustomerId, sFirstName, sLastName As String

With Sheets("Customers")

    'Open a connection to SQL Server
    conn.Open "Provider=SQLOLEDB;Data Source=TESTpc\SQLEXPRESS;Initial Catalog=ExcelSQLServerDemo;Trusted_connection=yes"

   'Skip the header row
    iRowNo = 2

    'Loop until empty cell in CustomerId
    Do Until .Cells(iRowNo, 1) = ""
        sCustomerId = .Cells(iRowNo, 1)
        sFirstName = .Cells(iRowNo, 2)
        sLastName = .Cells(iRowNo, 3)

        'Generate and execute sql statement to import the excel rows to SQL Server table
        conn.Execute "INSERT into dbo.Customers (CustomerId, FirstName, LastName) values ('" & sCustomerId & "', '" & sFirstName & "', '" & sLastName & "')"

        iRowNo = iRowNo + 1
    Loop

    MsgBox "Customers Exported To Database"

    conn.Close
    Set conn = Nothing

    End With
End Sub

Even though there are more efficient ways to export the data. This example is just for your reference purpose.

Upvotes: 0

Related Questions