June 12, 2008 at 11:26 am
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
June 12, 2008 at 9:09 pm
Why not use "Get External Data"? Much easier... look it up in Excel Help.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 12, 2008 at 9:28 pm
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?
June 13, 2008 at 9:46 am
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
June 13, 2008 at 9:52 am
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?
June 13, 2008 at 9:54 am
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
June 13, 2008 at 10:04 am
Thanks guys. This seems to be a much more palatable and viable option.
June 13, 2008 at 10:38 am
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
Change is inevitable... Change for the better is not.
June 15, 2008 at 12:58 pm
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]
June 15, 2008 at 5:02 pm
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
Change is inevitable... Change for the better is not.
June 16, 2008 at 6:49 am
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]
June 16, 2008 at 1:25 pm
I would be interested in anything you've got. There will be future changes on additional spreadsheets.
Thanks
June 16, 2008 at 1:36 pm
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
Change is inevitable... Change for the better is not.
June 16, 2008 at 1:55 pm
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?
June 16, 2008 at 2:13 pm
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