1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Macro to Read in Text File - Help R

Discussion in 'Business Applications' started by sirdoris, Aug 28, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. sirdoris

    sirdoris Thread Starter

    Joined:
    Feb 3, 2007
    Messages:
    5
    Hi,

    I'm hoping someone with a fair understanding of Excel and VBA can give me a hand here :)

    I don't really use Excel much, even less the VBA side of things (more web type things).

    Here is the problem, I receive a file of tabulated data in Word format which I need in Excel, with the data in the correct cells. Currently someone is transcribing this manually which is taking about a week every month :eek:

    It is a fixed file format (columns always start in the same place) so can be saved as a txt file, but being fixed format there is no delimiter so Excel dosen't know what to do with it and just sticks the whole thing in cell A,1.

    I have read that there is a funtion workbooks.opentext which might be able to help but i'm unsure of the parameters and how to implement this :eek:

    I'm using Excel 2000 with the built-in VBA 6 (I think).

    Any help is gratefully received :)
     
  2. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Hi there, welcome to the board!

    Yes, Excel 2000 has VBA 6.0. Is there any way you could upload a sample of the text files you're working with here, along with a previously formatted file in Excel? Also, what would be the scope of this solution? Are you looking for an add-in? Just a routine? Would it be manually called? If so, how? Through a custom menu? Toolbar? Macro menu? Keyboard shortcut? Would you like to give the user the ability to pick out the text file to format into Excel?
     
  3. sirdoris

    sirdoris Thread Starter

    Joined:
    Feb 3, 2007
    Messages:
    5
    Hi, Thanks for the quick reply.

    This is a sample of the extract with example data, the "?" and "x" are real values in the file:

    Code:
        LIST OF ????????? ????????? ??????? FOR MONTH OF  APRIL     2007                                                        Page :     1
        FOR ??????? MEMBERS
        PARENT DEPARTMENT : ???? - ??? ???
               EMPLOYER   : ????
    
    
                                                         ANNUAL         LUMP SUM                NATIONAL    AWARDING
    ???              SURNAME                 INTS     ????????????    ????????????   AMOUNTS   INSURANCE   DEPARTMENT     LAST-DAY     DOB
    ???  REFERENCE                                      PAYMENTS        PAYMENTS      TOTAL      NUMBER    REFERENCE      OF SERV
    
    X     001AABC    SMITH                     L A          999.99          0.00        999.99 xxxxxxxxx    CPD1001      15/03/1979 15/03/1956
    
          001DDC9    JONES                     C G          999.99          0.00        999.99 xxxxxxxxx    CPD1001      15/03/1979 15/15/1949
    
    X     002AADD    RICH                      B M            9.99      9,999.99      9,999.99 xxxxxxxxx    W669Z0BBBBBC 15/03/1979 15/10/1960
    
    X     00999D1    BLOGGS                    J             69.00      8,799.99      8,799.99 xxxxxxxxx    CPD1002      19/04/1990 15/11/1969
    
    Basically I'm hoping it will be possible to get the data (and headings?) into a worksheet, 1 data item per cell.

    As for how to accomplish this, like I said I'm not too hot on Excel. I assumed it would work as a macro that you could call from the Tools-> Macro->Macros menu in Excel.

    My initial thought was to read in the file from a specific location & filename on the local machine, and pull the into a new worksheet, possibly trimming white spaces etc using VBA before puitting them into the cells of the workbook. Is this possible?

    Thanks for any help you can offer :)
     
  4. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Can you possibly zip a copy of the word file and how you would like it looking in Excel? We should be able to just cut out the middle text file, one less step. It doesn't need to be full of data, nor correct. Just need to know what to look at and where to bring it into and how you want it to look.
     
  5. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Why can't you just open the textfile in Excel and select "Fixed Width" as your data type and manually add your break lines?

    Regards,
    Rollin
     
  6. ganero

    ganero

    Joined:
    Apr 3, 2008
    Messages:
    2
    Hello all, sorry for raising this topic again,
    i have same question about this macro & excel (2003), i need to take data from a text files, i want to take all data from certain column and put it in excel in certain column too. please look at my sample attached file, sorry i dont bring the real file, but this file has same format in it.
    please give me some hint how to accomplish this.
    thank you very much,
    ganero
     

    Attached Files:

  7. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Open the text file in Excel (File --> Open --> Change File type to .txt and select the file)

    When Excel opens it should ask you if you want to delimit the columns manually or using a character such as a space or tab. Quite simple to do.

    Regards,
    Rollin
     
  8. ganero

    ganero

    Joined:
    Apr 3, 2008
    Messages:
    2
    thank you,
    i'll try it.
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/616508

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice