August 29, 2008 at 4:41 am
Folks, I'm new to SQL server 2005 and as I write this, it may be more of a server config question than SQL Server related. We're running some jobs that populate Excel files resident on the server with data. We then have to manually open the Excel files from client laptops in order for some Auto-open macros to run and complete the formatting/prep of the workbooks.
What we want to do is export the data and use SSIS to open the spreadsheet causing the auto_open macro to be executed. Currently we don't seem to be able to do this, all we can do is open the files as a flat file (SQL server doesn't recognise them as excel). I suspect this is because Microsoft Office is not installed on the server. Can anyone advise what software we should install to allow this to happen or whether we have missed some feature of SQL Server that would allow us to do this.
Can we just install certain office dll's or do we need the whole package? Is MSOffice not being installed a red-herring?
Any guide appreciated.
Dan
August 29, 2008 at 6:11 am
You would need Excel installed.
What you are planning is probably not a good idea. Opening an Excel document from within a service context can be full of problems. You will be opening a UI from a hidden desktop - what if there is an error and the document cannot be closed without clicking on something? Doing what you are suggesting is a very common cause of applications hanging.
I would suggest you either use the excel object model and write a bit of code to do your formatting, or you export to template workbooks that are already correctly formatted. Either will be a safer approach than trying to get an auto-executing macro to run on a service desktop.
August 29, 2008 at 7:15 am
Michael, thanks for the advice. I can imagine exactly what you describe happening. Unfortunately the Excel sheets are formatted dependant on the data that is populated into them so only some of it can be done ahead of adding the data.
I have done some other research and what would your view on this approach be;
Installing the Office interop PIA files on the server, helps us avoid installing the whole MSOffice or Excel software (as this is something my organisation would prefer we avoid). Then using the interop capabilities in some way via a vb or SSIS project (maybe that's what you mean by writing code to use the excel object model)?
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply