+ Reply to Thread
Results 1 to 8 of 8

text file into Excel when opening Excel file or template

  1. #1
    Registered User
    Join Date
    05-06-2014
    Location
    Keizer OR
    MS-Off Ver
    Excel 2010, Excel 2003
    Posts
    11

    Question 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

  2. #2
    Valued Forum Contributor
    Join Date
    10-26-2008
    Location
    Birmingham, UK
    MS-Off Ver
    All versions up to 2010
    Posts
    1,025

    Re: text file into Excel when opening Excel file or template

    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

  3. #3
    Registered User
    Join Date
    05-06-2014
    Location
    Keizer OR
    MS-Off Ver
    Excel 2010, Excel 2003
    Posts
    11

    Re: text file into Excel when opening Excel file or template

    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

  4. #4
    Registered User
    Join Date
    05-06-2014
    Location
    Keizer OR
    MS-Off Ver
    Excel 2010, Excel 2003
    Posts
    11

    Lightbulb Re: text file into Excel when opening Excel file or template

    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

  5. #5
    Valued Forum Contributor
    Join Date
    10-26-2008
    Location
    Birmingham, UK
    MS-Off Ver
    All versions up to 2010
    Posts
    1,025

    Re: text file into Excel when opening Excel file or template

    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
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    05-06-2014
    Location
    Keizer OR
    MS-Off Ver
    Excel 2010, Excel 2003
    Posts
    11

    Re: text file into Excel when opening Excel file or template

    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

  7. #7
    Valued Forum Contributor
    Join Date
    10-26-2008
    Location
    Birmingham, UK
    MS-Off Ver
    All versions up to 2010
    Posts
    1,025

    Re: text file into Excel when opening Excel file or template

    Thanks for the feedback Mark.

  8. #8
    Registered User
    Join Date
    05-06-2014
    Location
    Keizer OR
    MS-Off Ver
    Excel 2010, Excel 2003
    Posts
    11

    Re: text file into Excel when opening Excel file or template

    It is frustrating finding a post that fits the problem I'm trying to solve and there is either no answer, or no feedback to a response on the quality of the answer. It really helps to find responses that say how well something resolved an issue and explains how some of the customizable parts of the code work. And I'm saying all this in hopes that future people who land here carry on the courtesy of providing some informative replies when they make posts here or in other forums. The usefulness of a forum is in direct relationship to what us users of the forum put into it. The admins can't do it all.
    Last edited by MarkInKeizer; 07-17-2014 at 04:08 PM. Reason: gramatical error

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel renaming file with a No.1 extension when opening excel template
    By molesy in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 09-12-2013, 10:45 AM
  2. How do I stop automatic opening template of any excel file
    By john55 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-28-2011, 06:06 AM
  3. Replies: 1
    Last Post: 05-26-2010, 07:56 AM
  4. Replies: 0
    Last Post: 11-08-2007, 04:14 AM
  5. Error:Invalid File format,while opening an Excel Template file
    By Saurabh in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-17-2005, 04:39 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1