Help - sp_OaCreate Excel from TSQL

[JPGMT]

I'm having problems creating and saving an Excel object.

I think that my TSQL syntax is wrong. My issue is not having to kill my process, it's the fact the I'm not getting a new Excel object.

I can create a Word document in TSQL and both in VBS, but not an Excel file in TSQL.

It fails and Excel is in the task manager but nothing else happens.

I have to kill the processes, from my Exe/Dll viewer "ProcExp"
(It's a cool app, that saves me from rebooting to free up my system)

Anyway, here is both my VBS and TSQL code.

TIA

JeffP...

'vbs code......saved in Notepad This works....
/*-------------------------------------
Dim myexcel
set myexcel = createobject("Excel.Application&quot
myExcel.Application.UserName = "JeffP"
myExcel.Application.Workbooks.Add
myExcel.Application.ActiveWorkbook.SaveAs ("Me.xls&quot
myExcel.Application.ActiveWorkbook.Save
myExcel.Workbooks.Close
myExcel.Application.Quit
'end vbs code
*/-----------------------------------------

--begin tsql This fails......
declare @Obj int ,@file varchar(100) ,@user varchar(100) ,@res int
,@sheet int ,@saveas varchar(255) ,@save varchar(255)

set @user = 'Jackx'
set @file = 'me.xls'

-- change the path if you want to test....
set @saveas = 'ActiveWorkbook.SaveAs("C:\Documents and Settings\jeff\My Documents&quot'
set @save = 'ActiveWorkbook.Save'

exec @res = sp_oacreate 'excel.application' ,@obj output
print 'Obj'
print @res
print @obj
print '-------'

exec @res = sp_oamethod @obj ,'workbooks.add' ,@sheet output
print 'add'
print @res
print @sheet
print '-------'

exec @res = sp_oamethod @obj ,@saveas
print 'saveas'
print @res
print @file
print '-------'

exec @res = sp_oamethod @obj ,'ActiveWorkbook.Save'
print 'save'
print @res
print @file
print '-------'

exec @res = sp_oamethod @obj ,'Workbooks.Close'
print 'close'
print @res
print @file
print '-------'

exec @res = sp_oamethod @obj ,'quit'
print 'quit'
print @res
print @sheet
print '-------'

exec @res = sp_oadestroy @obj
print 'end'
print @res
--end tsql

 

[JPGMT]

Solution: Use 8x3 file path names.