Question About Breaking Text into Columns

[No Spam]

We have been given 10 fixed length text files from a client. Each one
has the same "mapping" - about 35 fields for each record. Is there
any way to create a macro to break the files up automatically or do I
have to go through the Text Import Wizard (File - Open - change Files
of Type to text) and manually parse the text for each one? Thanks a
million in advance!

[Don Guillett]

Perhaps data>text to columns might prove useful.

--
Don Guillett
SalesAid Software
[email protected]
"No Spam" <[email protected]> wrote in message
news:[email protected]...
> We have been given 10 fixed length text files from a client. Each one
> has the same "mapping" - about 35 fields for each record. Is there
> any way to create a macro to break the files up automatically or do I
> have to go through the Text Import Wizard (File - Open - change Files
> of Type to text) and manually parse the text for each one? Thanks a
> million in advance!
>
>

[Dave Peterson]

If you record a macro when you do it once, maybe you could just re-run that
macro for the other files.

Or maybe you could get all the text files into one worksheet and then just do
the data|text to columns once.

No Spam wrote:
>
> We have been given 10 fixed length text files from a client. Each one
> has the same "mapping" - about 35 fields for each record. Is there
> any way to create a macro to break the files up automatically or do I
> have to go through the Text Import Wizard (File - Open - change Files
> of Type to text) and manually parse the text for each one? Thanks a
> million in advance!

--

Dave Peterson

[Earl Kiosterud]

This sounds like a one-time job of several similar files. Record a macro
where you've gone through the Text Import Wizard and opened one file. Then
add some code like:

Dim FileName as String
FileName = Application.GetOpenFileName
Workbooks.Open FileName:=FileName ... (rest of this statement from the
recorded macro

The macro will have recorded all the specifications for your fixed-length
file, and you won't have to go through the Wizard again. Read more on Excel
and Text files at http://www.smokeylake.com/excel/textfiles.htm
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"No Spam" <[email protected]> wrote in message
news:[email protected]...
> We have been given 10 fixed length text files from a client. Each one
> has the same "mapping" - about 35 fields for each record. Is there
> any way to create a macro to break the files up automatically or do I
> have to go through the Text Import Wizard (File - Open - change Files
> of Type to text) and manually parse the text for each one? Thanks a
> million in advance!
>
>

[Earl Kiosterud]

And if you don't want to mess with macro code at all, you might be able to
record one macro where you open the file going through the wizard, then
rename the files so the next file has the same name as the first, then run
the macro again. It's not exactly elegant, and you'll have to be careful
with the renaming, or you might lost track of what's what.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Earl Kiosterud" <[email protected]> wrote in message
news:%[email protected]...
> This sounds like a one-time job of several similar files. Record a macro
> where you've gone through the Text Import Wizard and opened one file.
> Then add some code like:
>
> Dim FileName as String
> FileName = Application.GetOpenFileName
> Workbooks.Open FileName:=FileName ... (rest of this statement from the
> recorded macro
>
> The macro will have recorded all the specifications for your fixed-length
> file, and you won't have to go through the Wizard again. Read more on
> Excel and Text files at http://www.smokeylake.com/excel/textfiles.htm
> --
> Earl Kiosterud
> mvpearl omitthisword at verizon period net
> -------------------------------------------
>
> "No Spam" <[email protected]> wrote in message
> news:[email protected]...
>> We have been given 10 fixed length text files from a client. Each one
>> has the same "mapping" - about 35 fields for each record. Is there
>> any way to create a macro to break the files up automatically or do I
>> have to go through the Text Import Wizard (File - Open - change Files
>> of Type to text) and manually parse the text for each one? Thanks a
>> million in advance!
>>
>>
>
>