Avatar of Dale Fye
Dale Fye
Flag for United States of America asked on

How do I display SQL Server data in Excel?

I'm a relative newbie to Excel, but am an Access expert and can make my way around in VBA.


I've got a client who has an Excel workbook which contains a number of worksheets, one of which is a set of data imported from a CSV file.  The other worksheets are basically a bunch of pivot tables used to analyze the data from the CSV file.  The down side is that he has to download the csv file (one job at a time) from a 3rd party add-in to Great Plains (Dynamics).  I have a job which downloads all of this data from the applicable tables in the GP database into a separate SQL Server used for reporting.


What I would like to do is add a worksheet (Sheet1) into his workbook which would:

1.  Display all of the jobs in a dropdown list, from a view in reporting database of SQL Server.

2.  On the click of a button on that first page, it would run a SQL Server stored procedure (passing in the Job # selected above) and would update the recordset displayed in Sheet 2 (hidden) to only include those records which match the selected job #.


Ideally, I would like to use a DSN-less connections to the SQL Server.

I already have a UserID/Password combination which has read-only permissions to the view, SP and the Table(s) in question.


Thanks

Dale


Microsoft ExcelMicrosoft SQL ServerMicrosoft Office

Avatar of undefined
Last Comment
arnold

8/22/2022 - Mon
Cédric Tielemans

Menu: Data > Get Data > From DataBase > from SQL..

Dale Fye

ASKER
Cedric, is that going to cause me to have to setup a DSN connection to the server?
Cédric Tielemans

Hi Dale, don't know sorry. This works for me but I didn't setup the sql server :).
Your help has saved me hundreds of hours of internet surfing.
fblack61
arnold

Much depends
you can setup a DSN (system) or you can use the SQL connection servername\instance username and password...
depending on your need, you may want to setup a dedicated read-only user that you will use in excel as data here is visible...
ste5an

My default approach:

Create an Excel document as template. Open that, copy the data from your sproc into a dedicated data sheet as plain table. Run an prepared Excel macro in that template base on that data page to create (or update) the necessary pivots.

The benefit of this approach is that you can always create new copy to play with and you don't have external links stored in Excel.

Otherwise, I would consider using a user form and doing it in Excel. So you can use a DSN-less connection for the form and use VBA to copy the data.
Dale Fye

ASKER
@ste5an,
"I'm a relative newbie to Excel ", from my original post.

Not sure what this means, or how to accomplish it.
"Run an prepared Excel macro in that template base on that data page to create (or update) the necessary pivots"

Let's start from point #1. I'd rather not use a user form.  Would prefer to use controls embedded in a worksheet.  How do I setup a dropdown list control on a worksheet, that uses a SQL Server view as it's data source (with a DSN-less connection)?

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ste5an

I'm a relative newbie to Excel, but am an Access expert and can make my way around in VBA.
Is Access or user form as controlling part really out of play here? It is the simpler way to do it. A user form in Excel is pretty much the same. A user control directly on a sheet is different.

In this case you need your own data sheet for the job list. Then you can bind your combo box or data validation control to this list. Add a button to start the execution of your sp to update the user data sheet.

How to use the forms controls on a worksheet in Excel.

p.s. I personally thing the user experience with a form is better. Just my 2 cent.
ASKER CERTIFIED SOLUTION
Dale Fye

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
arnold

When editing query, you can parameterize a cell or multiple cells to be passed to the
 query...

much depends on how far you want to go. you could on the sql server side create functions to do what you want and then on the excel side, run the function with parameters pulling the data..
I think based your comment, you have new ideas on what can be done.