.txt to Excel Spreadsheet.

[Cordury]

I receive daily invoice paid/process reports from a third party vendor, so there is little hope that they will change the format of their files.

I simply would like to read the .txt files in Excel. When I copy and paste the data, and use the Text to Columns function, there are serious format issues and there is too much data to manually fix. Below is an example of the .txt report:

UTILITY VENDOR UTILITY ACCOUNT NUMBER ------- LOCATION ------- FROM TO DATE DATE DATE AMOUNT

CITY OF DUBOIS BU 04198 A 08418 DU BOIS PA 041231 041231 041231 050120 012313 12.54
MILLCREEK TOWNSHI 9284 A 14582 ERIE PA 050101 050331 050110 050131 050124 62.00
MILLCREEK TOWNSHI 5711 A 07445 ERIE PA 050101 050331 050110 050131 050124 62.00
CITY OF MARTINSVI 5004512 A 08036 MARTINSVIL VA 041116 041215 041230 050122 050114 338.62

Ideally I would like to have a column for Utility Vendor, Account Number, Location Service Dates, and Amounts... However, I would gladly settle for Account Number, Check Date, and Amount.


I am assuming I need to use vba but would an array be possible?

Any suggestions would be appreciated.
Thanks,
Cord

 

[Cordury]

If someone could let me know how to post a pic/image of the actual report, I will do it. The original post does not look very good.

 

[PaulRD]

Yes, VBA can do what you want. Not sure what you mean by array, can you elaborate?

Paul D
pimpin' aint easy

 

[Cordury]

I apologize that I was not clear. I was curious is an the Array function could separate the data in the format that I need.

 

[PaulRD]

an array function is merely a storage container. So yes, you could read the text file in an array. But the cells in excel could also be used like an array so you could just read the text into excel.

 

[Cordury2]

Was the report ever received?

 

[123FakeSt]

We are going to have to determine if the file is fixed width or have a delimiter. Its hard to tell from the post.

The early bird gets the worm, but the second mouse gets the cheese.

 

[PaulRD]

It looks fixed width

Paul D
pimpin' aint easy