Exporting to Excel Script
-
Trying to add two tables to excel via a stored procedure. Using this SP. Works with no errors. But it does not produce a file? Can anyone tell me what is wrong? I need to add each of the two tables to a separate sheet in one Excel file.
The stored procedure:
EXEC ExportToExcel @server = '.',
@uname = 'sa',
@QueryText = 'SELECT au_fname FROM pubs..authors',
@filename = 'c:\ImportToExcel.xls'
*/
IF OBJECT_ID('ExportToExcel') IS NOT NULL DROP PROC ExportToExcel
GO
CREATE PROCEDURE ExportToExcel (
@server sysname = null,
@uname sysname = null,
@pwd sysname = null,
@QueryText varchar(200) = null,
@filename varchar(200) = 'c:\ImportToExcel.xls'
)
AS
DECLARE @sqlserver int,
@QueryResults int,
@CurrentResultSet int,
@object int,
@WorkBooks int,
@WorkBook int,
@Range int,
@hr int,
@Columns int,
@Rows int,
@indColumn int,
@indRow int,
@off_Column int,
@off_Row int,
@code_str varchar(100),
@result_str varchar(255)
IF @QueryText IS NULL
BEGIN
PRINT 'Set the query string'
RETURN
END
-- Sets the server to the local server
IF @server IS NULL SELECT @server = @@servername
-- Sets the username to the current user name
IF @uname IS NULL SELECT @uname = SYSTEM_USER
SET NOCOUNT ON
EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @sqlserver OUT
IF @hr <> 0
BEGIN
PRINT 'error create SQLDMO.SQLServer'
RETURN
END
-- Connect to the SQL Server
IF @pwd IS NULL
BEGIN
EXEC @hr = sp_OAMethod @sqlserver, 'Connect', null, @server, @uname
IF @hr <> 0
BEGIN
PRINT 'error Connect'
RETURN
END
END
ELSE
BEGIN
EXEC @hr = sp_OAMethod @sqlserver, 'Connect', null, @server, @uname, @pwd
IF @hr <> 0
BEGIN
PRINT 'error Connect'
RETURN
END
END
SELECT @result_str = 'ExecuteWithResults("' + @QueryText + '")'
EXEC @hr = sp_OAMethod @sqlserver, @result_str, @QueryResults OUT
IF @hr <> 0
BEGIN
PRINT 'error with method ExecuteWithResults'
RETURN
END
EXEC @hr = sp_OAMethod @QueryResults, 'CurrentResultSet', @CurrentResultSet OUT
IF @hr <> 0
BEGIN
PRINT 'error get CurrentResultSet'
RETURN
END
EXEC @hr = sp_OAMethod @QueryResults, 'Columns', @Columns OUT
IF @hr <> 0
BEGIN
PRINT 'error get Columns'
RETURN
END
EXEC @hr = sp_OAMethod @QueryResults, 'Rows', @Rows OUT
IF @hr <> 0
BEGIN
PRINT 'error get Rows'
RETURN
END
EXEC @hr = sp_OACreate 'Excel.Application', @object OUT
IF @hr <> 0
BEGIN
PRINT 'error create Excel.Application'
RETURN
END
EXEC @hr = sp_OAGetProperty @object, 'WorkBooks', @WorkBooks OUT
IF @hr <> 0
BEGIN
PRINT 'error create WorkBooks'
RETURN
END
EXEC @hr = sp_OAGetProperty @WorkBooks, 'Add', @WorkBook OUT
IF @hr <> 0
BEGIN
PRINT 'error with method Add'
RETURN
END
EXEC @hr = sp_OAGetProperty @object, 'Range("A1")', @Range OUT
IF @hr <> 0
BEGIN
PRINT 'error create Range'
RETURN
END
SELECT @indRow = 1
SELECT @off_Row = 0
SELECT @off_Column = 1
WHILE (@indRow <= @Rows)
BEGIN
SELECT @indColumn = 1
WHILE (@indColumn <= @Columns)
BEGIN
EXEC @hr = sp_OAMethod @QueryResults, 'GetColumnString', @result_str OUT, @indRow, @indColumn
IF @hr <> 0
BEGIN
PRINT 'error get GetColumnString'
RETURN
END
EXEC @hr = sp_OASetProperty @Range, 'Value', @result_str
IF @hr <> 0
BEGIN
PRINT 'error set Value'
RETURN
END
EXEC @hr = sp_OAGetProperty @Range, 'Offset', @Range OUT, @off_Row, @off_Column
IF @hr <> 0
BEGIN
PRINT 'error get Offset'
RETURN
END
SELECT @indColumn = @indColumn + 1
END
SELECT @indRow = @indRow + 1
SELECT @code_str = 'Range("A' + LTRIM(str(@indRow)) + '")'
EXEC @hr = sp_OAGetProperty @object, @code_str, @Range OUT
IF @hr <> 0
BEGIN
PRINT 'error create Range'
RETURN
END
END
SELECT @result_str = 'exec master..xp_cmdshell ''del ' + @filename + ''', no_output'
EXEC(@result_str)
SELECT @result_str = 'SaveAs("' + @filename + '")'
EXEC @hr = sp_OAMethod @WorkBook, @result_str
IF @hr <> 0
BEGIN
PRINT 'error with method SaveAs'
RETURN
END
EXEC @hr = sp_OAMethod @WorkBook, 'Close'
IF @hr <> 0
BEGIN
PRINT 'error with method Close'
RETURN
END
EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
PRINT 'error destroy Excel.Application'
RETURN
END
EXEC @hr = sp_OADestroy @sqlserver
IF @hr <> 0
BEGIN
PRINT 'error destroy SQLDMO.SQLServer'
RETURN
END
GO
-
This was removed by the editor as SPAM
-
This might be a dumb question, but why dont you use Excel and MSQuery to pull the data into your spreadsheet rather than using SQl to export and create a file?
cheers
Mat
-
All processing of monthly data is automated. Right now the import and export of files is still manual. And mistakes made delay reporting processing. So I am trying to automate these steps also.
-
Microsoft Knowledge Base Article - 319951
HOW TO: Transfer Data to Excel by Using SQL Server Data Transformation Services
http://support.microsoft.com/default.aspx?scid=kb;en-us;319951&Product=sql2k
From the article:
If you select the drop and re-create the destination table option, the drop command fails the first time that you run the package because the table does not exist; however, the export succeeds.
If you select create the destination table without the drop and re-create option, the create command fails on subsequent executions because the table already exists; however, the export succeeds.
----------------------------------
We tried this. Was not a good thing. We either ended up with nothing or we had duplications.
We ended up dropping back and punting. We built an Access database with a one button form. My suggestion is to create an access DB with ODBC calls. Then you can do the imports/exports and reporting via access. I know it isn't pretty, but you can put a text box on a form that fires all the functions that you want to do. Then schedule the DB to open at a certain time. I've done this with a few different server based DB's.
Just throwing in my $0.02........
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
We have had the alot of problems with DTS jobs creating Excel files. We finally went the other way. Using a FILEDSN built into the spreadsheet and creating a button the retrieved the data all problems were solved. Using a filedsn, the user did not have to have ODBC setup either, all is built into the spreadsheet.
We just created the excel file with nothing but the button and made it read only so everytime the user needed the spreadsheet, they opened it, hit the button, it retrieves that data. No problems. We have even been able to put drop down parameters in the spreadsheets.
-
my best solution is still to create a stored procedure that creates this file. My reason is this. In this process a cleanup of data is first performed. Then at the end of the process items with missing information are extracted to a temp table and exported to Excel, sent to a vendor to have the missing information filled in. This file is then returned, imported back into Sequel Server and further processing continues.
So this export to Excel is done within a custom application that runs these scripts in the background. Users may not know Excel or Access or SQL at all. All processing is done in the background.
-
You can have the AccDB pick up and send the table to the end-user with the docmd.sendobject command as an excel spreadsheet. You can even fire the access using the xp_cmdshell.
The import can remain a DTS import. The end user doesn't need to know how you exported the spreadsheet.
Or are you trying to build a packaged app?
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
I am sort of building a packaged app. The issue is that it still needs to be user friendly, not used by programmers or DBA's. But it needs to be very flexible. Data elements and reports change often. But the process is repeated many times. So they make changes to a part of the process and just click the process step to produce a string of events. I could possible make a DTS package and fire it from a script.
-
The code you posted is the same piece of code that I am trying to get work with only one modification. You removed the credits!
/*
Version: SQL Server 7.0/2000
Created by: Alexander Chigrik
http://www.MSSQLCity.com/ - all about MS SQL
(SQL Server Articles, FAQ, Scripts, Tips and Test Exams).
This stored procedure can be used to insert the result set of the
particular select statement into Excel file (c:\ImportToExcel.xls,
by default).
You can pass the server name, user name, user password, the select
statement to execute, and the file name to store the results set,
as in the example below:
*/
Anyhow, I get a different error, but his thread is old and I'm not sure what I'm going to do to try to get it into Excel.
-
Im trying to use this code as well, and I am running into a problem where the code errors when issueing he offset command. (Ive added error display code below).
EXEC @hr = sp_OAGetProperty @Range, 'Offset', @Range OUT, @off_Row, @off_Column IF @hr <> 0 BEGIN EXEC spDisplayOAErrorInfo @SQLServer, @hr PRINT 'error get Offset' RETURN END
This gives the following error;
OLE Automation Error Information
HRESULT: 0x0000275d
Source: ODSOLE Extended Procedure
error get Offset
Has anyone any idea why this is not working ?
Im running Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Personal Edition on Windows NT 5.0 (Build 2195: Service Pack 3) . In the interim, Im going to patch SQL Server to sp3a to see if that improves things.
Cheers,
JE (sudmill)
-
FYI, I'v patched MSSQL to 3a and this code is still not working beyond the getoffset call.
FYI, Im running Microsoft Excel 2000 v9.0.6926 SP-3. Note ( the code is identical to that pasted in the original post, apart from my added error calls and the original header (c) !)
JE
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply