October 19, 2011 at 11:19 am
Hi SQL World.
I need some assistance with trying to export a table to a excel spreadsheet. I starting playing around with the following query that works but I am hoping to get some assistance with covering all aspects of what I am trying to complete.
I need to schedule a DTS on the first of the month to an excel spreadsheet so users can access the spreadsheet. I also need the DTS to create a new excel workbook rather than just over writing the previous worksheet. Is that possible?
I started playing around with this query, but it seems like its only going to over write an existing worksheet, rather than creating a new workbook.
EXEC sp_makewebtask
@outputfile = 'c:\testing.xls',
@query = 'Select name_f, name_l, building from csc_comm_data',
@colheaders =1
--@FixedFont=0,@lastupdated=0,@resultstitle='Testing details'
Does anyone know a method that would tackle what I am trying to do?
October 20, 2011 at 8:47 am
By new excel workbook, are you referring to a new tab in an existing excel file, or are you referring to a new excel file?
If it's the former, I don't have any suggestions.
If it's the latter, then you're in luck 😛 What you can do is, create a template file that has the structure of your excel file that you want. Then, have two file system tasks. The first one will take the current excel file, and rename/move it to an archive folder. The second will take the template file, and rename/copy the file to the name you want. Then you have a blank excel spreadsheet to play with.
October 21, 2011 at 7:25 am
Couldn't you just add a date into the file name to make it different from the previous file(s).
DECLARE @fileDate VARCHAR(20)
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
SET @fileName = 'c:/testing' + '_' + @fileDate + '.xls'
This would create a new file with a date attached rather than overwriting it each time.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply