I've google a bit about this can't seem to find a straight answer. Is there a way to code SQL to open an Excel workbook? This workbook is already linked to my table and I'm getting the data I need. This is just for more of a convenience instead of having to click through folders to get to the template.
Hi
Please try this and let me know
Query to Open Excel Sheet
select * from OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml;Database=ExcelFileNameWithPath.xlsx;HDR=YES','select * FROM [Sheet1$]')
No dice. I'm receiving the following error.
Ad hoc access to OLE DB provider 'Microsoft.ACE.OLEDB.12.0' has been denied. You must access this provider through a linked server.
I'm VERY new to SQL so I'm not exactly sure what this means.
I should also clarify that I'm not trying to write to the excel file or even read from the excel file. I simply want to physically open the workbook for me to refresh some data that's in it manually.
so your workbook is linked to your sql server table? and you want sql server to refresh the data in Excel?
no dice. you will have to use some other programming language
Are users using this excel to update data in sql server?
what that means is
In SQL Server you can create LINKED SERVERS
to access objects outside SQL Server on disk
Please google to see how to create LINKED SERVERS
and choose Excel type ..
If you have any issues please let me know
I will try my best to help you
Thanks
I'm not trying to read or write to it. I just want to open the file up. There are two sheets in that workbook. One of them is linked to a table in SQL and the other one is full of pivots that I need to refresh. I just want SQL to open the file for me instead of me fumbling through folders to get to it.
This really doesn't make sense...
SQL Server is a service running on a server - it doesn't really know anything about the file system and it cannot 'open' a file...
A linked server is an object that you define in SQL Server to tell it where another database lives - so you can then access the objects in that database. In this case the 'database' is actually an Excel spreadsheet...so now SQL Server knows about this Excel spreadsheet as a linked server and you can access the data using normal SELECT statements.
I believe what you are asking for is some type of shortcut in SSMS that you can click on that will open that file up for you. If that is what you are asking for - then you need to create a 'Solution' and a 'Project'...
Make sure you can view the Solution Explorer - create a new project - and then in that project add an existing item. Navigate to the Excel spreadsheet (you have to show all files) and add the spreadsheet.
Once added - any time you have that project open in SSMS you will be able to double-click on the item and it will open in Excel.