June 17, 2022 at 3:54 pm
This code works on other 2005 SQL Sever but my 2017 SQL Server gives error.
/* Excel Automation*/
declare @xlApp integer, @rs integer
Declare @FileName varchar(500)
declare @xlWorkbooks integer
declare @xlWorkBook integer
declare @xlWorkSHEET integer
declare @xlCell integer
declare @xlLastRow INTEGER
DECLARE @sql VARCHAR(4000)
execute @rs = dbo.sp_OACreate 'Excel.Application', @xlApp OUTPUT
select @rs, @xlapp
execute @rs = master.dbo.sp_OAMethod @xlApp, 'Workbooks', @xlWorkbooks OUTpUT
--execute @rs = master.dbo.sp_OAMethod @xlWorkBooks, 'Add', @xlWorkBook OUTPUT, -4167
select @xlWorkBooks
set @FileName = 'c:\temp\book2.xlsx'
--execute @rs = master.dbo.sp_OAMethod @xlWorkbooks, 'Open', @xlWorkBook OUTPUT, @FileName
declare @error_description varchar(255)
declare @object int
,@hr int
,@src varchar(1000)
execute @rs = master.dbo.sp_OAMethod @xlWorkbooks, 'Open', @xlWorkBook OUTPUT, 'C:\temp\book2.xls'
exec master.dbo.sp_OAGetErrorInfo @xlWorkBook, @src out, @error_description out
select @rs, @FileName, @xlWorkBook, @error_description, @src
--getting Error when opening file
---2146827284 - Microsoft Excel cannot access the file 'C:\temp\Book2.xls'. There are several possible reasons: • The file name or path does not exist. --• The file is being used by another program. • The workbook you are trying to save has the same name as a currently op
execute @rs = master.dbo.sp_OAMethod @xlWorkBook, 'ActiveSheet', @xlWorkSheet OUTPUT
select @xlWorkSHEET
execute @rs = master.dbo.sp_OAMethod @xlApp, 'Quit'
execute @rs = master.dbo.sp_OADestroy @xlWorkSheet
execute @rs = master.dbo.sp_OADestroy @xlWorkBook
execute @rs = master.dbo.sp_OADestroy @xlWorkBooks
execute @rs = master.dbo.sp_OADestroy @xlApp
June 17, 2022 at 3:55 pm
Error
---2146827284 - Microsoft Excel cannot access the file 'C:\temp\Book2.xls'. There are several possible reasons: • The file name or path does not exist. --• The file is being used by another program. • The workbook you are trying to save has the same name as a currently op
June 18, 2022 at 4:43 pm
Have you granted the SQL Server service user access to that folder?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 18, 2022 at 5:18 pm
and... why you using Excel on the server? It is unsupported and likely you are breaking the licensing terms for Office/Excel.
July 8, 2022 at 2:51 am
This was removed by the editor as SPAM
July 13, 2022 at 4:03 am
This was removed by the editor as SPAM
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply