SQL2005 to create EXCEL documents-CodePudding

 DECLARE @ ExcelPath nvarchar (1024); - Excel file path 
DECLARE @ strErrorMessage VARCHAR (1000); - output error message 

DECLARE @ hr INT. 
DECLARE @ objExcel INT. 
DECLARE @ objWorkBooks INT. 
DECLARE @ objWorkBook INT. 
DECLARE @ CMD NVARCHAR (4000); 
The SET @ strErrorMessage=' '; 

Set @ ExcelPath='c: \ ss. XLS' 


-- to create Excel. 
 the Application objectThe EXEC @ hr=sp_OACreate 'Excel. Application, @ objExcel OUTPUT; 
IF @ hr=0 
The BEGIN 
- create WorkBooks objects 
The EXEC @ hr=sp_OAGetProperty @ objExcel, Workbooks, @ objWorkbooks OUTPUT; 
IF @ hr=0 
The BEGIN 
- use the Workbooks objects Add to Add a Workbook 
The EXEC @ hr=sp_OAGetProperty @ objWorkbooks, 'Add', @ objWorkBook OUTPUT; 
IF @ hr=0 
The BEGIN 
- use the SaveAs method to save 
SET @ CMD='SaveAs ("' + @ ExcelPath + ') '
The EXEC @ hr=sp_OAMethod @ objWorkBook, @ CMD. 

- shut off the Workbook 
IF @ hr=0 
The EXEC @ hr=sp_OAMethod @ objWorkBook, 'Close'. 
The ELSE 
The SET @ strErrorMessage='save the Excel file failed! '; 
END 
The ELSE 
The SET @ strErrorMessage='add work thin failed! '; 
END 
The ELSE 
The SET @ strErrorMessage='failed to create work thin! '; 
END 
The ELSE 
The SET @ strErrorMessage='create Excel object failed! '

IF @ hr=0 
The BEGIN 
The EXEC @ hr=sp_OAMethod @ objExcel, 'Quit'; 
END 
- eliminate the Excel object 
IF @ hr=0 
The EXEC @ hr=sp_OADestroy @ objWorkbooks; 
IF @ hr=0 
The EXEC @ hr=sp_OADestroy @ objExcel;

Under the environment of Windows 7 + sql2005 + office2003 program execution to line 28
The EXEC @ hr=sp_OAMethod @ objWorkBook, @ CMD.
, EXCEL document has not been saved to disk C, executed @ the value of hr is=- 2146827284

I in the same configuration on another machine can run under created automatically by EXCEL documents,

The younger brother puzzled by the question for several days, seek expert advice, I have urgent need, thank you!

CodePudding user response:

Windows 7? May involve the issue of UAC, you save to disk C plate other than a try,

CodePudding user response:

reference 1st floor ccrun response:

Windows 7? May involve the issue of UAC, you save to disk C plate other than a try,

Tried or not...
This statement is in the process of setting up the EXCEL, but can't save the document

CodePudding user response:

As essentially can save data for a high version of excel well among other file formats, concrete is a kind of text format files, the extension is forgotten

CodePudding user response:

reference KFRGHT reply: 3/f

used seems to save the data for a high version of excel well among other file formats, concrete is a kind of text format files, extension is what forget

I need now is to use SQL to create real EXCEL documents

CodePudding user response:

This essentially created document itself, can only be saved in essentially a place on the disk of the machine