Ad Clicks :Ad Views : Ad Clicks :Ad Views : Ad Clicks :Ad Views : Ad Clicks :Ad Views :
    Excel formulas and tutorials

    Excel formulas and useful tools

    Home / VBA / Connecting to SQL Server in VBA Excel

    Connecting to SQL Server in VBA Excel

    /
    /
    /
    1316 Views

    Building automated reports in Excel by connecting directly to an ERP database is a good shortcut to have fresh and accurate information to base your decisions on. This method facilitates data processing and manipulation in VBA before results and conclusions are displayed and a further availability for organizing, calculations and sorting after Excel tables are populated.

    Connecting to SQL Server

    To connect to a database server you must switch to code writing and in Project Explorer (left side or visit View menu if not visible) select ThisWorkbook and add the following code in here:

    Public Con As New ADODB.Connection

    Private Sub Workbook_Open()

    Dim r As New ADODB.Recordset
    Con.ConnectionString = “driver={SQL Server};Server=SQLServerName;uid=UserName;pwd=Password”
    Con.Open
    Con.CommandTimeout = 20
    Con.Execute “table”

    End Sub

    You also have to go to Tools / Preferences… to have the Microsoft ActiveX Data Objects 2.5 Library checked in order for this type of connection object to be available.

    The connection works only after you save and reopen the Excel file as the connection is made only when the file is opening.

    Connecting to SQLServer VBA

    Using the connection to the database

    The “r” variable as ADODB.Recordset can be used after this in any Procedure or Function to query the tables of the database and process the outputs. Don’t forget to define the variable as ADODB.Recordset in every macro.

    If the connection breaks for any reason, the Excel file should be closed and reopened in order to reset it. Excel files which contain VBA macros must be saved with .xlsm or .xls extensions as .xlsx doesn’t save the VBA code behind the Excel spreadsheets.

     
    • Facebook
    • Twitter
    • Google+
    • Linkedin
    • Pinterest

    1 Comments

      Leave a Comment

      Your email address will not be published. Required fields are marked *

      This site uses Akismet to reduce spam. Learn how your comment data is processed.

      This div height required for enabling the sticky sidebar