Calling an Excel Macro from a Stored Procedure
-
I have coded a stored procedure that shells out to a windows command file that calls a vbscript file that ultimately calls an excel macro. When the shell command runs, it just hangs and never returns, but if I run the command file directly, it works fine. I am thinking it might be a rights issue, but I'm not sure.
My vbscript is as follows:
Dim args, objExcel
Set args = WScript.Arguments
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open args(0)
objExcel.Visible = False
objExcel.Run "CreateReport", args(1), args(2)
objExcel.ActiveWorkbook.Save
objExcel.ActiveWorkbook.Close(0)
objExcel.Quit
I know it it hanging on the line Set objExcel = CreateObject("Excel.Application"). If anyone can give me some clue as to why this is not working, I would appreciate it.
Thanks.
-
excel automation is tough sometimes.
i'm pretty sure the issue is the first time a user runs Excel, a dialog comes up asking for your initials. I think the initials are used for various things like change tracking and stuff.
after they've been entered once, the issue goes away, as the info is stored in the registry.
so you need to open Excel once as the account that SQL Service is using...it might be an account that never logs into the operating system, so you probably need to change the account running the service:
when you do any automation through SQL like this,
SQL Server uses either the account set up as the proxy account, or if that is left blank(the default) it uses account it starts with to try and access the resource:
or if the above was blank, the account in services:
when you run the vbscript, it uses your login credentials, so the registry entry is there.....but when you run it via sp_CmdShell, it's running as one of those acocunts i identified, which has not used excel yet.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thanks for your reply, Lowell. As far as I can tell, the user that runs the command shell from SQL is mssql2k8. I logged directly onto the server as mssql2k8, launched Excel and did the initial thing. I can run the command file directly as mssql2k8, but when I call the command file using xp _ cmdshell it still hangs when launching Excel.Application. I am still at a loss here.
-
I am finally back to working on this. I created a proxy user with full admin rights. This allows Excel to launch, but it is still hanging. Out of frustration, I tried a different route, trying to get some type of feedback:
Declare @ExcelObject int, @status decimal, @Workbook int, @MacroFile varchar(200),
@msg varchar(255), @rsn varchar(255)
SET @MacroFile = 'E:\TML\AutomatedReports\Macros\PT65.xlsm'
Exec @status = sp_OACreate 'Excel.Application', @ExcelObject output
IF @status=0 EXEC @status = sp_OAMethod @ExcelObject, 'WorkBooks.Open', @Workbook output, @MacroFile
IF @status <> 0
BEGIN
exec sp_OAGetErrorInfo @ExcelObject, @rsn out, @msg out
print @status
print @msg
print @rsn
END
EXEC sp_OAMethod @ExcelObject, 'Application.quit'
EXEC sp_OADestroy @Workbook
EXEC sp_OADestroy @ExcelObject
This returns the following generic error:
-2146827284
Microsoft Office Excel cannot access the file 'E:\TML\AutomatedReports\Macros\PT65.xlsm'. 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 curre
Microsoft Office Excel
When I log onto the server as my proxy user, I can open the Excel file with no problem and I can even run the command file I was originally trying and it works perfectly. For some reason when shelling from a query window it cannot open the file.
I verified in Task Manager that it is using my proxy user.
Anyone have any ideas I can try?
Thanks in advance.
Kevin
-
I agree that automating Excel can be tricky and it's been a long time since I've done it, but it seems to be really funky on early vs late binding. Instantiate in every way you can think of, and most likely, the clunkiest stinkin code is what will work.
It isn't much help, I'm sorry, but, it's my experience, and something to try!
-
Kevinray64
Can you tell us a litle bit more about what you want do do.
I usually use VBA (you can access a remote SQL-server with OLEDB
which is very usefull if you don't want to install Excel on the server).
The user has Excel installed as a client and can fetch data from the server
(a useraccount with restricted access)
Sometimes I use VB.net
Best luck
Gosta M
-
The big picture: We have several reports in SSRS that work fine, but our users are not happy with the way the formatting is done when exporting the data to Excel. I was tasked to come up with an alternate solution. For proof of concept, I modified one of the stored procs to create text files and I created an Excel macro to format the data exactly as they want it. I call the macro from a vbs file.
This works great when I run the vbs file from either explorer or from a dos prompt. But when I call the vbs file from my stored proc it just hangs.
I then tried a different route using the sp_OA* procedures and get the error posted above.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply