# Excel Macro to Read in Text File - Help R



## sirdoris (Feb 3, 2007)

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 

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 

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

Any help is gratefully received


----------



## Zack Barresse (Jul 25, 2004)

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?


----------



## sirdoris (Feb 3, 2007)

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:


```
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


----------



## Zack Barresse (Jul 25, 2004)

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.


----------



## Rollin_Again (Sep 4, 2003)

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


----------



## ganero (Apr 3, 2008)

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


----------



## Rollin_Again (Sep 4, 2003)

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


----------



## ganero (Apr 3, 2008)

thank you,
i'll try it.


----------

