VBA Code to Import Data from SQL Server into Excel

by

VBA Code:

Sub ImportDataFromSQLServer()
    Dim conn As Object
    Dim rs As Object
    Dim sqlQuery As String
    Dim connectionString As String
    Dim rowNum As Long
    
    ' Set the SQL query to retrieve the data
    sqlQuery = "SELECT * FROM TableName" ' Replace TableName with the actual table name
    
    ' Set the connection string for the SQL Server database
    connectionString = "Provider=SQLOLEDB;Data Source=ServerName;Initial Catalog=DatabaseName;User ID=Username;Password=Password"
    ' Replace ServerName, DatabaseName, Username, and Password with the actual values
    
    ' Create a new connection object
    Set conn = CreateObject("ADODB.Connection")
    
    ' Open the connection to the SQL Server database
    conn.Open connectionString
    
    ' Create a new recordset object
    Set rs = CreateObject("ADODB.Recordset")
    
    ' Execute the SQL query and retrieve the data into the recordset
    rs.Open sqlQuery, conn
    
    ' Set the starting row number to paste the data in the Excel worksheet
    rowNum = 2
    
    ' Loop through each record in the recordset and copy the data to Excel
    Do Until rs.EOF
        ' Copy each field value from the recordset to Excel
        For i = 1 To rs.Fields.Count
            Worksheets("Sheet1").Cells(rowNum, i).Value = rs.Fields(i - 1).Value ' Change the sheet name as desired
        Next i
        
        ' Move to the next row in Excel
        rowNum = rowNum + 1
        
        ' Move to the next record in the recordset
        rs.MoveNext
    Loop
    
    ' Close the recordset
    rs.Close
    
    ' Close the connection to the SQL Server database
    conn.Close
    
    ' Inform the user that the data has been imported
    MsgBox "Data imported from SQL Server successfully."
End Sub

Check All VBA Codes

Join Our Telegram Group techguruplus telegram group Join Our WhatsApp Group techguruplus whatsapp group
Nazim Khan - Author Image

Nazim Khan (Author) 📞 +91 9536250020
[MBA in Finance]

Nazim Khan is an expert in Microsoft Excel. He teaches people how to use it better. He has been doing this for more than ten years. He is running this website (TechGuruPlus.com) and a YouTube channel called "Business Excel" since 2016. He shares useful tips from his own experiences to help others improve their Excel skills and careers.

Leave a Comment