April 27, 2005 at 6:57 am
I'm new to SQL Server and am trying to query a database directly to return results from multiple tables into a Excel 2003 workbook for further analysis.
Up until 2003, excel had the SQL.REQUEST function, which would have handily done all the work in one short line.
However, this is not included in Excel 2003 and an add-in cannot be downloaded for it. Thanks MS.
The only other ways I have found of doing this (from Excel support forums) seem to be via complex VBA macros - something of which I have no experience.
As this was once such an easy thing to do, surely there must still be a simple way of doing this? It seems like a common enough task, so surely MS aren't expecting me to have to learn VBA to do it....
Any pointers gratefully received.
April 27, 2005 at 1:22 pm
1) Use the "export data" wizard - right click on table you want exported, choose "all tasks" - "export data".
2) Select your server and database in "data source" - go to next screen
3) Under destination drop-down select "excel 2003"
4) Type in path and file name under "file name" - if file doesn't exist, it'll be created for you.
5) On the next screen select "use a query to specify data to transfer" - and build your query. You can use the "query builder" button to make the job easier.
6) Follow through on all the filters etc. building your query and hit "run immediately" - once task is finished open up excel file and voila - your report is there!
I may have missed a step or two but if I have you should be able to figure it out - good luck!
**ASCII stupid question, get a stupid ANSI !!!**
April 28, 2005 at 12:19 pm
Or, depending on how separate the databases are and what type, you could simply build a view, combining data from the different databases. In Excel use Data\Import External Data\Import Data. The Data Source dialog will be displayed. At this point you can create a new data source that will connect to the database with the new view. Then select the query you built. That was the hard part; from now on you merely select the data source or query from the import data menu and it will query the db and display the data. This method does not require the two step process of exporting from the database and then opening in Excel.
I do this with two separate SQL Server databases on the same instance, however, the same could be accomplished from a single SQL Server database with a linked server connection to another database.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply