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 / Reading from SQL table in VBA Excel

    Reading from SQL table in VBA Excel

    /
    /
    /
    744 Views

    Reading from SQL tables usually means that there is a need for supplementary data processing and manipulation before conclusions and information being provided to the decision making person. Excel is the best application to satisfy this need and some very useful and ergonomic reports can be developed in order to provide fresh and accurate information.

    Using the connection to sql server defined in the previous post, queries to database tables can be conducted in a very simple way. The same technique can be used to connect to all kinds of databases, just change the SQL driver in the connection definition.

    Reading from SQL

    Define ADODB variable to store query data

    Consider “r” defined as “Dim r As New ADODB.Recordset” the variable where the query data will be stored after executing the inquiry from the database.  Now you can use the formula to execute the query like tihis:

    r.Open “Select *From Table Where ID>1”, ThisWorkbook.Con

    Navigating inside the query variable read from SQL

    All you need to do after the query was executed is navigating from row to row inside the ADODB variable. Let’s say you need to display the contents of the ID field form the table in the first row of an Excel workbook.

    i=3

    While Not r.EOF

    Sheet(1).cells(i,1)=r(“ID”)

    i=i+1

    r.MoveNext
    Wend
    r.Close

    The ADODB variable acts like an array of rows and needs to be processed line by line in a loop, the navigation forward being made by the MoveNext command and the EOF indicator becomes TRUE if the end of the array is reached.

     
    • Facebook
    • Twitter
    • Google+
    • Linkedin
    • Pinterest

    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