Updating Excel Spreadsheet from SQL Database using a query

I would like to use the query below in an Excel Spreadsheet so users can update the spreadsheet and get updated data.
I would share this Excel document with a handful of people here at the office who would need to access the data for a daily task.

If you feel there is a better way to accomplish this please help me out with your suggestions.

--This is a query that passes in the current date to a query

declare @today char(10)
declare @startdate char(10)
declare @month char(2)
declare @day char(2)
declare @year char(4)

--Set Todays Date
set @month=DATEPART(month, GETDATE())
set @day=DATEPART(day, GETDATE())
set @year=DATEPART(year, GETDATE())
set @today=rtrim(@month)+'/'+rtrim(@day)+'/'+rtrim(@year)

--Set Start Lookup Date
set @month=DATEPART(month, GETDATE()-1)
set @day=DATEPART(day, GETDATE()-1)
set @year=DATEPART(year, GETDATE()-1)
set @startdate=rtrim(@month)+'/'+rtrim(@day)+'/'+rtrim(@year)


--THIS RETURNS THE PREVIOUSLY SHIPPED ITEMS ON CLOSED SHIPPERS A
SELECT left(b.invtid,3)+'-'+right(rtrim(b.invtid),
      len(b.invtid)-3) as InventoryId,
      SUM(b.QtyShip) AS TOTALSOLD,
      b.INVTID,
      a.descr
      
      


from inventory a,soshipline b,soshipheader c,itemsite d, Customer e
where c.CpnyID = b.CpnyID and c.ShipperID = b.ShipperID

and b.invtid=a.invtid and a.invtid=d.invtid and c.CustId=e.CustId and


            c.CpnyID ='0001' AND
            c.CustID LIKE '%' AND
            c.CustID <> 'AAA1000' AND
            c.Status ='C' AND
            b.InvtID LIKE 'g/h%' AND
            c.SiteID LIKE 'WEST' AND
            c.sotypeid<>'tr' AND
            c.cancelled<>'1'and
            c.OrdDate >= @startdate AND
            c.OrdDate <= @today
      GROUP BY b.INVTID,a.descr,a.lastcost,d.PrimVendID
      ORDER BY d.PrimVendID,b.INVTID,a.descr,a.lastcost
armgonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

brianmfallsCommented:
Have you looked into the PHPExcel class?  I wrote a blog post on it recently.  It's rather simple to read a spreadsheet into an array, or to write an array into a spreadsheet.  All you would have to do is to read your SQL into an array.  :)  The documentation for PHPExcel is very good.  There is a link to PHPExcel on the blog post.

http://blog.brianfalls.com/2015/09/11/reading-xls-and-xlsx-into-a-php-array/
MlandaTCommented:
Yes. You can use the Excel External Data Connections functionality. It allows you to connect a spreadsheet to a database table / view / query and users just need to click on Data -> Refresh each time they want to get an updated set of data. This works very well and I would advise that you make your query a Database View. This then allows you to modify the logic of the query without having to update the Excel files all over (e.g. if you add a column to the view, it would automatically appear in Excel when users next do the refresh). You can find information on how to set this up here: https://support.office.com/en-us/article/Connect-a-SQL-Server-database-to-your-workbook-22c39d8d-5b60-4d7e-9d4b-ce6680d43bad

There is a nice option with this where using Microsoft Query, you can call a stored procedure with parameters. The values for the Parameters can come from Excel cells. Each time the user changes the cell values, Excel can automatically refresh the data against the stored procedure.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
armgonAuthor Commented:
Thanks for the replies. I will jump on this today. Much appreciate the input.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.