I have text file with about 300 lines items that are not delimited but
the width of each columns is fixed and do have the value. For example,
cell 1 is char(1-2), cell 2 is char(2-14)....
I want to be able to copy the whole thing in Excel, run a macro that
split the string into the different cell.
I can manually do it using the Text to Column function and setting the
width one by one but it's time consuming since this import is occuring
everyday.

Can you help?

Thanks,

If you want to split the data into multiple columns before you send it to
Excel, you could do that in a query, and export the query.

For example, to generate a column named C1 from the first 2 characters in a
field named f, type an expression like this into the Field row in query
design
C1: Trim(Left([f],2))

Then in the next column of the Field row:
C2: Trim(Mid([f],3,12))

And so on.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<(E-Mail Removed)> wrote in message
news:0b5e552b-34a1-4eba-afe6-(E-Mail Removed)...
>I have text file with about 300 lines items that are not delimited but
> the width of each columns is fixed and do have the value. For example,
> cell 1 is char(1-2), cell 2 is char(2-14)....
> I want to be able to copy the whole thing in Excel, run a macro that
> split the string into the different cell.
> I can manually do it using the Text to Column function and setting the
> width one by one but it's time consuming since this import is occuring
> everyday.
>
> Can you help?
>
> Thanks,

On Fri, 16 Nov 2007 16:22:06 -0800 (PST), (E-Mail Removed) wrote:

>I have text file with about 300 lines items that are not delimited but
>the width of each columns is fixed and do have the value. For example,
>cell 1 is char(1-2), cell 2 is char(2-14)....
>I want to be able to copy the whole thing in Excel, run a macro that
>split the string into the different cell.
>I can manually do it using the Text to Column function and setting the
>width one by one but it's time consuming since this import is occuring
>everyday.

I'd (strongly!!!) suggest setting up and storing a File Import Specification
(using the Advanced button on the import wizard). You can map the character
positions to fieldnames. You don't need to do this in Excel - it can be done
directly in the import.

Take a look at the Help for "Import or Link Data and Objects" or go a Google
Groups search of these newsgroups for "Import Specification". I was noodling
around in the Help file and the Microsoft Knowledge Base but wasn't able to
quickly find a detailed description, but I know it exists somewhere... :-{(



John W. Vinson [MVP]