updating xls from sql server

Question

updating xls from sql server

whitlander

SSChasing Mays

Points: 637
More actions
August 9, 2010 at 8:36 am

#225900

I have several stored procedures that are ran monthly. At the moment we manually copy the output from these SPs into to several spreadsheets; each containing a datasheet and a pivot. Once the data are copied in, the pivot table is refreshed, and the spreadsheets saved using the current month as a the filename.
I have been trying to work through the tutorial here:
http://www.sqlservercentral.com/articles/Integration+Services+%28SSIS%29/61542/
...but have hit too many errors. If anyone can help me in automating any part of this - SSIS or otherwise - it would be greatly appreciated.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

malcolm.o-nions Valued Member Points: 61 More actions · Answer 1

Here's a couple of suggestions

If you're not using the 64-bit version of SQL Server you could create a linked server to a copy of each Excel report file and then you can treat the data sheet as if it were a table issuning DELETE, INSERT etc commands within your stored procedure, finally using xp_cmdshell to copy the file to a month-named version

EXEC sp_addlinkedserver

@server = 'XLREPORT1',

@provider = 'Microsoft.Jet.OLEDB.4.0',

@srvproduct = 'Excel',

@datasrc = 'FullPathToExcelFile',

@provstr = 'Excel 8.0'

DELETE XLREPORT1...DataSheetName$

INSERT INTO XLREPORT1...DataSheetName$

SELECT .......

EXEC xp_cmdshell 'copy FullPathToExcelFile NewPathToCopyOfFile'

Or you could write VBA code in the Excel sheet that calls the stored procedure via ADO and writes the data out to the data sheet

There's some additional good stuff on SQL to Excel at

http://www.simple-talk.com/sql/t-sql-programming/sql-server-excel-workbench/

whitlander SSChasing Mays Points: 637 More actions · Answer 2

your first suggestion seems much simpler. trying that now...

thanks for you time

drew.allen SSC Guru Points: 76985 More actions · Answer 3

Have you considered writing an SSRS report? You can use a table to get the datasheet and a matrix to get the pivot table. You can the schedule it to run monthly, export automatically to Excel, and even put a timestamp in the file name.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA