Solved

write SQL Server 2 Excel

Posted on 2012-04-07
5
311 Views
Last Modified: 2012-04-27
I know there must be a way to write to  cells within excel from sql Server.  This is possible to do in access.

I need VBA code (Function) that will write a stored procedure results to a user defined location on a excel workbook.

Lets say the SP returns 6 records (each records contains five fields).  I want to dump the results to cell "A1" or any other location in the workbook.

                  Cell A1   Cell B1    Cell C1       Cell D1    Cell E1
  record 1  FName   LName     Address     city          State
  record 2  FName   LName     Address     city          State
.
.
.
  record 6  FName   LName     Address     city          State
0
Comment
Question by:tru-numbers
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 34

Expert Comment

by:Norie
ID: 37819197
So you need to do it in Access?

You could do this using VBA and ADO in Excel.

If you do need it in Access you could set up a pass through query to return the results, then export the results to Excel.
0
 

Author Comment

by:tru-numbers
ID: 37819705
Yes that is a possibility, but the user database is in SQL Server 2005.  The DB has 1000 users worldwide.
0
 
LVL 34

Expert Comment

by:Norie
ID: 37819724
Which is a possibility?

The pass through query in Access or pulling directly from SQL Server using VBA in Excel?
0
 

Author Comment

by:tru-numbers
ID: 37820136
I must pull diectly from sql server!
0
 
LVL 34

Accepted Solution

by:
Norie earned 500 total points
ID: 37820285
You can use VBA with ADO for that.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question