text file into Excel when opening Excel file or template

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

Hi Mark
Are you able to post a relative sample of your text file and preferred layout so I can take a look for you. I have recently built an extract from MSProject in text format and formatted it in Excel using VBA.
Tony

DEIDENTIFIEDBRSDATA_20140714b.txt

Data is deidentified so it will look a little strange. It's pipe delimited which is how it will end up coming out of SQL Server. If the only way to do this is to write VBA I can do most of that. One question I do have is how do I trigger the import code in Excel when the worksheet opens? I'm used to Access and VB6 with form_load events and various other nice tricks to get something to run first. So far I have not found and equivalent when Excel opens. I'm sure it's there I'm just not asking the right question when I Google it. I would prefer to not have this run requiring the customer to push a button. All I want to leave in the customers hands is put the two files in the same folder and double click the Excel file.

Thanks
Mark

1) I resolved my SSIS issue which was the following error -- [Execute SQL Task] Error: Failed to acquire connection "Excel Connection Manager". Connection may not be configured correctly or you may not have the right permissions on this connection.
Turns out that's a setting.
SSIS_FailedToAcquireConnectionFix.jpg


2) That doesn't resolve my ugly Excel issue. So I'm going to try two things.
A) My SSIS package currently builds an Excel file (it's ugly) I'm going to put a pretty file in the folder on the server instead. Then point the load task of SSIS at that and see what I get.
B) One of the Excel experts at my work says I can build a template with an ODBC connection in it to a text file. It will then open, import the data and display it as formatted in the template.

I'll report back on the success of these methods. We've got several jobs we need to automate the production of the Excel file and be left with something useable without a lot of human formatting work later.

Mark

Hi Mark
The attached Workbook will rn an automatic macro when it is opened called SQL Extract. If you store the SQL extract file on your desktop and call it SQL Extract then ope n the SQL Extract Workbook the text file will be imported automatically using the pipe character as a delimeter. If you need further formatting then please let me know.
Good luck.
Tony

ARGK that's exactly what I need, thank-you!
I'm assuming the spreadsheet is simply built with the proper column widths and then this line of the VBA
TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
needs each column put in the Array.

I'm also figuring this property is critical .AdjustColumnWidth = True

For anyone else who comes across this, if you prefer a different delimiter that is done here. .TextFileOtherDelimiter = "|"

Another tidbit I like With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\deleteme\sqlextract\SQLExtract.txt", Destination:=Range("$A$1"))

I'm assuming change the range and the top left corner of where it puts the data will move.

Thanks
Mark

Thanks for the feedback Mark.