Using VBA to pull SQL data into Excel?

  • I have a multi-tab Excel file and I would like to use VBA to pull in different tables from SQL Server to different tabs. I want to use Windows authentication for security. Does anyone have any simple straight-forward examples on how to do this?

    Thank you

  • Why not use "Get External Data"? Much easier... look it up in Excel Help.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Agreed - with the get external data and connection based on windows auth - you've got it all covered and no VBA needed.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • I'm using Excel 2003. Do you mean "Import External Data"? Please be more specific. We have many spreadsheets with data imports to more than 10 tabs each. Can the data be easily refreshed?

    Thank you

  • If you use database queries/web queries from the import external data, yes - refreshing data is very simple. By default - it would refresh each time you open the spreadsheet, and you can use the "refresh data" option to check at any given time....

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Setup an ODBC connection to your database.

    Click on data -> Import External Data -> New database query

    and select your database or an exsisting query

    The data can be easily refreshed by right clicking on the returned data and clicking refresh.

    Create a conneciton for each tab that you need data on.

    You can save these and they will refresh when you open the spreadsheet

  • Thanks guys. This seems to be a much more palatable and viable option.

  • montgomery johnson (6/13/2008)


    I'm using Excel 2003. Do you mean "Import External Data"? Please be more specific. We have many spreadsheets with data imports to more than 10 tabs each. Can the data be easily refreshed?

    Thank you

    Yes... same type of thing. Me, I write views in the database and then use the external data functionality to query the view. And, you can not only setup the external data to automatically refresh when you open the spreadsheet, you can have it automatically update every "x" minutes while the sheet is open. Throw in some conditional formatting and you can have a near real time "alarm" system... for example, I taught a telephone switch to write certain output to files and built an SQL system to automatically import those files... using the technique I just told you about, I could monitor everything on the switch just by looking for anything on the spreadsheet that was yellow or red.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • On the other hand, if your sheets need to be resized based on the number of rows returned, then automated "Get External Data" refreshes can be pretty hard to get working right. I think have VBA code (somewhere) for doing this if you still need it.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • rbarryyoung (6/15/2008)


    On the other hand, if your sheets need to be resized based on the number of rows returned, then automated "Get External Data" refreshes can be pretty hard to get working right. I think have VBA code (somewhere) for doing this if you still need it.

    Nah... sheets resize auto-magically.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (6/15/2008)


    rbarryyoung (6/15/2008)


    On the other hand, if your sheets need to be resized based on the number of rows returned, then automated "Get External Data" refreshes can be pretty hard to get working right. I think have VBA code (somewhere) for doing this if you still need it.

    Nah... sheets resize auto-magically.

    But pointers to them (like data ranges) do not.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • I would be interested in anything you've got. There will be future changes on additional spreadsheets.

    Thanks

  • rbarryyoung (6/16/2008)


    Jeff Moden (6/15/2008)


    rbarryyoung (6/15/2008)


    On the other hand, if your sheets need to be resized based on the number of rows returned, then automated "Get External Data" refreshes can be pretty hard to get working right. I think have VBA code (somewhere) for doing this if you still need it.

    Nah... sheets resize auto-magically.

    But pointers to them (like data ranges) do not.

    Actually, I think data-ranges can be made to be self expanding... it's been a very long while since I've had to do something like this so I'll have to try it... not 100% sure about it, yet...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I could swear that if you define a named range covering the query, it would update as the query does......

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Jeff Moden (6/16/2008)


    rbarryyoung (6/16/2008)


    Jeff Moden (6/15/2008)


    rbarryyoung (6/15/2008)


    On the other hand, if your sheets need to be resized based on the number of rows returned, then automated "Get External Data" refreshes can be pretty hard to get working right. I think have VBA code (somewhere) for doing this if you still need it.

    Nah... sheets resize auto-magically.

    But pointers to them (like data ranges) do not.

    Actually, I think data-ranges can be made to be self expanding... it's been a very long while since I've had to do something like this so I'll have to try it... not 100% sure about it, yet...

    I would be very interested if you could find a way to make that happen. I haven't tried it in Excel 2007, but in every other version that I have tried, the automatic data updates did overwrites, not inserts.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply