Open excel file from SQL Server stored procedure

Question

Hello,

I have a VBA code event in Excel file (the code runs automatically when the excel opens) that I want to run from SQL server SP, is there a way to open an Excel file from SP ?

Answer 1

You can access an Excel file from SQL Server it you set up a linked server to it. You need to install the ACE OLE DB driver. ACE does not come with SQL Server, but is a separate download (and is rather part of the Office family.)

I should warn you that this is not a trivial exercise, not the least because if it doesn't work, the error message gives very little clue about what is wrong. Through the years that I've been monitoring SQL Server forums, I have seen plentiful of questions from people who have not been able to get this to work. (And I have never tried it myself.)

As for what happens with your VBA code when you access the file from SQL Server, I don't know. But my gut feeling is that it will not be executed. One reason for this is that the Excel file is not opened in the same sense, as when you open it with Excel. Also important to keep in mind is that SQL Server runs as a server, and does not have a desktop, so SQL Server cannot open the Excel file to display the content.

As you may sense, I don't really want to encourage you to take this route, but I think you should reconsider and take a different path for what you want to achieve.

Answer 2

It is feasible to open an Excel file from a stored procedure (SP) in SQL Server, one way to achieve this is by utilizing the xp_cmdshell extended stored procedure. This extended stored procedure allows executing command-line commands within a stored procedure. By using xp_cmdshell, it is possible to run the command that opens the Excel file and triggers the VBA code event. An example of how this can be done:

EXEC xpcmdshell 'C:\Program Files (x86)\Microsoft Office\root\Office16\EXCEL.EXE

"E:\Your_Excel_File.xlsm"'

Answer 3

Hi @nd0911,

As @Erland Sommarskog already mentioned, it is possible to query MS Excel if you have Microsoft ACE OLEDB provider installed on a server where you have MS SQL Server instance running.

It will allow you to query Excel sheets via SELECT ... statement like a virtual database table on a file system. Though, I doubt it will allow you to run a VBA code.

Because you will have access to data in the Excel, maybe you can use T-SQL to replace VBA functionality.

Answer 4

When a new row added to some table in my SQL Server (by SP), I need to send this PDF, so I thought if the Excel file will open, a "Open Workbook" event ("event" in VBA is like a trigger in SQL Server) will do the rest (I have this VBA code running right now but I need to open the Excel file manually).

You could have a process that regularly polls this table for new rows and then opens the Excel file. You could schedule this process from Windows Task Scheduler. However, since Task Scheduler also runs sa a service, the lack of a desktop is still likely to constitute a problem. I have no deep knowledge of Task Scheduler, but you could ask around in a Windows forum.

But I have a feeling that for a robust solution, you should take Excel and Outlook out of the equation. It may still have to be an Excel file if users want to be able to edit the file. But for generating the PDF, it may be better to use a third-party library that can read Excel files. But now we starting to stray quite beyond SQL Server.