I need to try to open a text file that is 500 bytes per record, that is each
record is a dump from a mainframe and is 500 spaces long with no delimiters.
I know where each position of data will be, for instance in position 30 thru
34 is the zip code, 35 thru 38 is the 4 zip code extension 38 thru 53 is the
first name, etc.

So I need to transfer this data to an excel spreadsheet. I thought I could
loop through the file line by line extracting the data I wanted and placing
in variables, then moving to the spreadsheet and pasting those variables.
Or maybe just insert a comma in the text file to be used as a delimiter and
import the file into a template with the headers already set up. I'm
looking for some direction as to which way to go. Once I can decide that,
then I will be able to struggle thru the code with the help of this board.

In any case how can I open a file for reading, or if adding a comma editing.
Lets assume the text file has an absolute path of C:\Temp\TextFle.txt

Thanks in Advance

Have you tried the text import wizard? Click File/Open, select All Files for
the file type. Select your text file, you should see the text import wizard.
Using the fixed width option you can set the column breaks.

If you want a macro to perform the text import operation, you can turn on
the macro recorder while you perform the action and get an outline you can
work with (you may need to clean it up a little - the recorder records
everything you do, but you can learn how excel refers to particular tasks).
The recorder will hardcode your filename, check VBA help for the
GetOpenFilename method

"Striker" wrote:

I need to try to open a text file that is 500 bytes per record, that is each
record is a dump from a mainframe and is 500 spaces long with no delimiters.
I know where each position of data will be, for instance in position 30 thru
34 is the zip code, 35 thru 38 is the 4 zip code extension 38 thru 53 is the
first name, etc.

So I need to transfer this data to an excel spreadsheet. I thought I could
loop through the file line by line extracting the data I wanted and placing
in variables, then moving to the spreadsheet and pasting those variables.
Or maybe just insert a comma in the text file to be used as a delimiter and
import the file into a template with the headers already set up. I'm
looking for some direction as to which way to go. Once I can decide that,
then I will be able to struggle thru the code with the help of this board.

In any case how can I open a file for reading, or if adding a comma editing.
Lets assume the text file has an absolute path of C:\Temp\TextFle.txt

Thanks in Advance

Dim sLineOfText, sFirstName as String

Open "C:\Temp\TextFle.txt" For Input As #1

Do Until EOF(1)

Line Input #1, sLineOfText

sFirstName = Mid(sLineOfText, 38, 15)

msgbox sFirstName

Loop

Close #1


--
Kaak
------------------------------------------------------------------------
Kaak's Profile: http://www.excelforum.com/member.php...fo&userid=7513
View this thread: http://www.excelforum.com/showthread...hreadid=555826

I have an add-on to this question.

Instead of text files on your harddrive, is there a way for Excel t
access .html files in real-time? For example, could I have a cel
always display the end-of-the-day stock quote by looking up the htm
file of a financial website? I am familiar with VBA but not Excel, an
basically I am confused as to how to "transcend the gap" between .xl
files and .html files and somehow link them together

--
yl35
-----------------------------------------------------------------------
yl358's Profile: http://www.excelforum.com/member.php...fo&userid=3577
View this thread: http://www.excelforum.com/showthread.php?threadid=55582

Check out DataGet External Data New Web Query.

NickHK

"yl358" wrote in
message ...

I have an add-on to this question.

Instead of text files on your harddrive, is there a way for Excel to
access .html files in real-time? For example, could I have a cell
always display the end-of-the-day stock quote by looking up the html
file of a financial website? I am familiar with VBA but not Excel, and
basically I am confused as to how to "transcend the gap" between .xls
files and .html files and somehow link them together.


--
yl358
------------------------------------------------------------------------
yl358's Profile:
http://www.excelforum.com/member.php...o&userid=35776
View this thread: http://www.excelforum.com/showthread...hreadid=555826