Using VBA to pull SQL data into Excel?

Question

Post reply

Using VBA to pull SQL data into Excel?

montgomery johnson

Default port

Points: 1466
More actions
June 12, 2008 at 11:26 am

#188969

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

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

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

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)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

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?

montgomery johnson Default port Points: 1466 More actions · Answer 3

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

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?

steveb. SSC-Forever Points: 46733 More actions · Answer 5

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

montgomery johnson Default port Points: 1466 More actions · Answer 6

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

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)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

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]

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

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)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

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]

montgomery johnson Default port Points: 1466 More actions · Answer 11

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

Thanks

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 12

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)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 13

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?

RBarryYoung SSC Guru Points: 143329 More actions · Answer 14

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]