Currently I'm producing an Excel file with SSIS out of SQL Server and the result is ugly and needs a lot of formatting work. I'm also fighting issues with the SSIS Excel connection object and it appears that I may have a conflict between SSIS and the version of Office on my development system. So I'd like to leave SSIS or bcp to producing a text file, easy and fast solution on the server side and a lot less surprises there. So that leaves the issue of my customer opening the text file in Excel and having something a lot nicer to look at when it opens. So the theory is this, place text file in the same folder as the Excel file or Excel template, double click Excel and it sees the text file, imports it and displays it. Ideally when I build the Excel file I've already setup the column widths, header, etc for the end user so they don't have to.
Can this be done? I'm not afraid of VBA, have done a lot of it in Access over the years and have a few Excel projects under my belt. Neither of which get me to what I'm describing. One had the customer push a report button in Access and they got a pretty file afterward. The other the customer pushes a button and it imports 130 spreadsheets into SQL Server. I seem to get these projects about every five years so it's like I'm a newbie in Excel each time. Prior classes in Excel make me think this is an easier solution than a 300 line VBA project. For the life of me I can't think of how to get started on this.
The end customers of this don't have rights to my SQL Server and management doesn't want to change that. So I have to send them data in a text file.
Can you point me to some examples to get me started? Or a short description of approach?
I should point out the result set is between 300-400 rows, this isn't huge, and nature of the business behind the data, it should never be more than a 1,000 rows. It will not exceed max rowsize for a sheet. Excel versions vary, 2003, 2010 and soon 2013 all on Windows XP with a few 64 bit Windows 7 machines here and there. All Office versions are 32 bit.
Thanks
Mark
Bookmarks