Pasting Personality issues

[BBFB]

Hi,

I have a question that Google didn't answer, nor did the numerous PDF I
have
about excel programming, and of course, nor did my boss


Here you go!


I have an Excel 2003 workbook with a sheet.
I have a user form.
I have a TextBox in that user form.
I also have a text file outside of Excel.


When I copy paste manually the text file into any cell in the sheet,
the
text file gets spreads onto the cells pretty good - You know, most of
the
data has its own cell, etc., it looks decent enough to attempt to write
a
parser -by the way, my boss told me "its either write a parser or
parse the
327 word doc manually into excel" so of course, I feel constrained
to ask for
help ...

The problem arise when I have the text file pasted into the user form
text
box and then try to have a macro do some parsing. You see, the user
gets to
paste from the windows clipboard (in this case a 327 page word doc that
I
pasted into a text file first) into the user form textbox, and then
press the
Parse! button. The button calls a Parse macro that I wrote in another
module.


But when the textbox.text gets copied into a cell with code like ...

myTemp.Range(A7).Value = sData

All the text gets fitted into one cell.


I am trying to reproduce the effect that I get when I manually copy a
text
file into a cell : it gets spread into many cells more or less in a
readable
fashion.

So my Question is: How do I achieve this effect in coding?

Thank you for your help, kindly,

Antoine Dubuc
Best Buy For Business

 

[robertdeniro]

Could you post an example of your source data, as well as the kind of
output you want.

Rather than write your own parser, why not load the text into a hidden
instance of Word & use the Word object model to parse the text into
words, sentences, etc.

Of course, it would depend on the output you want.

 

[BBFB]

Here is an example, that is also based on the template I gotta parse.
I need only part of this data.



0593
L&P Chicago Heights Chicago Heights IL 60411




Leggett & Platt Inc

D-U-N-S® Number: 13-368-8262
Company Name: Leggett & Platt Inc
Also Known As: Morgan Marshall

Mail Address: 383 E 16th St
Chicago Heights, IL, 60411-3701
View Map

County: Cook
MSA: Chicago

Phone: 708-758-6300
Fax: 708-758-5858
WebAddress: www.leggett.com

Location Type: Branch
Plant/Facility Size: 205,000 Sq Ft
Foreign Trade: Import/Export
Year Established: 1989
Ownership: Public
Prescreen Score: Low Risk
Stock Ticker: LEG

Global Ultimate Parent: Leggett & Platt Inc
1 Leggett Rd
Carthage, MO, 64836-9649
417-358-8131
View Global Ultimate Profile





Employee Count:
(All Sites) 33,000
Employment:
(Individual Site) Current Year: 550
1 Yr Prior: 200 | Trend: 175.00
2 Yr Prior: 200 | Trend: 175.00
3 Yr Prior: 550 | Trend: 0.00
Sales:
(All Sites) $5,085,500,000
US (Actual)
Sales:
(Individual Sites) $58,428,150
US (Estimated/Modeled)



Executives: Mr Phillip A Rosenband - President
Mr Phillip A Rosenband - Owner
Mr Jeff Blau - Purchasing Director
Mr Stanley R Jewell - Chief Financial Officer
Mr Stanley R Jewell - Treasurer

Add Decision Maker

Sic Code(s): 25410203 - Shelving, office and store, wood (Primary)
25429904 - Shelving, office and store, except wood
Line of Business: Manufactures wooden & metal store fixtures
Product(s): STORE & DISPLAY FIXTURES & EQPT: Wood
STORE & DISPLAY FIXTURES & EQPT, EXC WOOD
Naics Code(s): 337215 - Showcase, Partition, Shelving & Locker Mfg
(Primary)

 

[Tom Ogilvy]

When you paste the word document into Excel directly, it isn't coming in as
a textfile - but a word document. Excel has build in functionality to try
to reproduce the general layout of the word document. When you assign it as
a string to a cell, then you get everything in one cell.

The solution would be to do a copy from the word document to the excel
worksheet. Then excel will be able to act as you said you are "pleased"
with.

 

[BBFB]

But, I actually -and need to- copy the word document to a text fil
(notepad) before copy pasting it in the user form text box from th
text file.

the reason is that user control is also part of future project an
requires this ability.

Thank you:

 

[Tom Ogilvy]

then your parse routine will have to break the sting up into the appropriate
pieces and write these pieces to the appropriat cells.


An example of such code (working directly on a text file, but the concepts
are the same) are at Chip Pearsons site for his import routine.

http://www.cpearson.com/excel/imptext.htm import/export text files