Line breaks when pasting text / No line breaks in forms 
Author Message
 Line breaks when pasting text / No line breaks in forms

Hi everyone,

I've searched everywhere for the solution to this and although I'd read many
solutions to similar problems, none of them sort my problem out. :)

I'm trying to paste data that I'm going to copy from email on a regular
basis, into Excel. I'm planning to run a macro that I've already written and
that will do a few weird and wonderful things with that data. Well it was
all working fine until I came across one today with a line break in.

The email is generated from an html form, and where the person had hit enter
in one of the fields, a line break had occured. So, when Excel pastes the
clipboard contents, it gets one more line than expected, so my primative
macro doesn't understand and ends up with the lines out of sync!

At first the macro pastes the clipboard contents that should be 9 lines long
into some cells. Then it seperates them out because each line has the field
name, followed by an '=' symbol and then data. It then takes the data and
puts it elsewhere and does some other stuff with it.

Ok, so I'm not explaining the problem very well! I'll try to ask exactly
what I need rather than waffling. :)

I want to find a way that I can either put in a formula or macro in Excel,
to detect and delete line break. Or maybe a way to paste the clipboard
contents and...well no, thinking about it, I WANT the line breaks at the end
of most lines-Just not the user generated ones! Now I'm really lost! Ok, so
I no longer want to detect line breaks because Excel won't know which ones I
want and which I don't. Hmmmmmmm.......

Ok, so maybe this is the wrong place for this question now because I'm going
to ask if there's a way to prevent people from entering line breaks in an
html document in the 'textarea' field on a form?

I'll find a suitable html related place to post this as well as the
Microsoft Excel group that I was about to post to, so hopefully between the
two someone will be able to come up with a solution!

I did try to write a formula that would check for the "=" symbol in every
line, and if it wasn't there, to add the contents of that cell to that of
the cell on the previous line. That worked, but then it meant having a
formula below that to put the contents of what's now on the wrong line, into
the correct place-Well I gave up because it wasn't going to work.

I think I need a whole new way of doing this!

Maybe I should just ask people not to push the enter key? ;)

Thanks for any help you can give me,
Geoff.



Mon, 28 Jun 2004 10:58:58 GMT  
 Line breaks when pasting text / No line breaks in forms
Geoff,

I'll give this a shot.

You're trying to copy text from the body of a generated email and paste the
values into Excel.  And you're facing trouble because someone wasn't nice
and decided to slip in an extra linefeed.  I hope I have the krux of the
issue.

Assuming you want to stay with the Copy/Paste, the Paste from the clipboard
will shade in a number of cells after the paste..this reflects the range
that has been pasted.  That range can be referred to in VBA by
Selection...it's the active selection.  That is, until you selection
something else.

One possibility is to look at all the cells you've just pasted, before you
parse them out, and look for the length.  If the length is 0 (extra Enter
key) you don't want it.  Or i guess you sould say if you don't find an "="
in it, the line is trash.

So the solution is to loop/iterate across all the cells in Selection and
test if ths is true.  if so, get rid of it.  Depending on what you have in
adjacent columns, you may be able to delete the entire row to remove the bad
label, or you may need to clear that cell and shift the other entries up
(though if you really think this can only happen at the end..then there will
be no shifting).  Bear in mind however that if you choose to delete the
entire row, you need to loop across BACKWARDS because deleting a row in that
selection while you're looping forward will often force it to process past
the intended range.

That can work fine.  The only other suggestion I'd have for you is to take a
look at the Outlook object model.  A more secure method might be to start
some Automation between Outlook and Excel, where you can get the body of the
email you want, test the entries line by line to make sure they meet your
validation criteria, and then paste valid entries into the cells.  it's more
of a pain to set up but it is more powerful than the clipboard.

Hope this helps,

Greg McIntire


Quote:
> Hi everyone,

> I've searched everywhere for the solution to this and although I'd read
many
> solutions to similar problems, none of them sort my problem out. :)

> I'm trying to paste data that I'm going to copy from email on a regular
> basis, into Excel. I'm planning to run a macro that I've already written
and
> that will do a few weird and wonderful things with that data. Well it was
> all working fine until I came across one today with a line break in.

> The email is generated from an html form, and where the person had hit
enter
> in one of the fields, a line break had occured. So, when Excel pastes the
> clipboard contents, it gets one more line than expected, so my primative
> macro doesn't understand and ends up with the lines out of sync!

> At first the macro pastes the clipboard contents that should be 9 lines
long
> into some cells. Then it seperates them out because each line has the
field
> name, followed by an '=' symbol and then data. It then takes the data and
> puts it elsewhere and does some other stuff with it.

> Ok, so I'm not explaining the problem very well! I'll try to ask exactly
> what I need rather than waffling. :)

> I want to find a way that I can either put in a formula or macro in Excel,
> to detect and delete line break. Or maybe a way to paste the clipboard
> contents and...well no, thinking about it, I WANT the line breaks at the
end
> of most lines-Just not the user generated ones! Now I'm really lost! Ok,
so
> I no longer want to detect line breaks because Excel won't know which ones
I
> want and which I don't. Hmmmmmmm.......

> Ok, so maybe this is the wrong place for this question now because I'm
going
> to ask if there's a way to prevent people from entering line breaks in an
> html document in the 'textarea' field on a form?

> I'll find a suitable html related place to post this as well as the
> Microsoft Excel group that I was about to post to, so hopefully between
the
> two someone will be able to come up with a solution!

> I did try to write a formula that would check for the "=" symbol in every
> line, and if it wasn't there, to add the contents of that cell to that of
> the cell on the previous line. That worked, but then it meant having a
> formula below that to put the contents of what's now on the wrong line,
into
> the correct place-Well I gave up because it wasn't going to work.

> I think I need a whole new way of doing this!

> Maybe I should just ask people not to push the enter key? ;)

> Thanks for any help you can give me,
> Geoff.



Mon, 28 Jun 2004 11:44:29 GMT  
 Line breaks when pasting text / No line breaks in forms
Greg,

Thanks for your full reply. I admit I'm a little unsure about some of what
you wrote but I'll go through it again when it's not 4am. :)

Yep your summary of the situation is spot-on.

Something I'm not sure about isd when you mention deleting lines without the
"=" in. I do actually need those lines, but I want them stuck on the end of
the line above.

I think that it's going to take so much effort that it's not really worth
it. I may hardly get anyone filling out the form anyway! I hope that's not
the case but it's a new website the form is on and I'm just trying to
prepare myself in case it gets busy, so I'm trying to get everything as
automated as possible to save time.

As you say, using the clipboard isn't the best way but I don't know any
other way. Maybe I should look into getting Outlook and Excel to communicate
as you suggest.

If there isn't a relatively simply solution, maybe it's best if I simply
look at what I'm copying to make sure it's ok? I doubt many people would
place a carriage return in those fields anyway. I WOULD love to get it
working better but I don't know enough to make it worth struggling over it.

I think a better way would be if I could prevent the carriage return in the
first place. I COULD make the field on the form just one line high, and I've
tried doing that, but people would probably get annoyed at not having enough
room to type all they wanted!

Thanks for your help. I will read your ideas tomorrow when I'm more awake.
:)

Geoff.



Mon, 28 Jun 2004 12:14:31 GMT  
 Line breaks when pasting text / No line breaks in forms

Quote:

> Hi everyone,

> I've searched everywhere - -

Please try to get to the point a bit faster.

Quote:
> I'm trying to paste data that I'm going to copy from email on a
> regular basis, into Excel.

So how does that relate to _HTML_? And what is the _original_ problem?
You're apparently describing some part of a (planned?) system, a part
that looks oddly designed, and there's little way of knowing how to
improve it without knowing its purpose in context.

Quote:
> Well it was all working fine until I came across one
> today with a line break in.

Huh? You processed lots of E-mail before you encountered the first line
break. Odd.

Quote:
> The email is generated from an html form, and where the person had
> hit enter in one of the fields, a line break had occured.

The technical side is under discussion elsewhere in this group right now,
and you might take a look at some points:
http://www.cs.tut.fi/~jkorpela/forms/textarea.html

Quote:
> I want to find a way that I can either put in a formula or macro in
> Excel, to detect and delete line break.

No, that's just your assumed technical approach to an unspecified _real_
problem. I'm sure that Excel macros are not what you _really_ want, just
an assumed tool... to _some_ end.

Quote:
> Ok, so maybe this is the wrong place for this question now because
> I'm going to ask if there's a way to prevent people from entering
> line breaks in an html document in the 'textarea' field on a form?

Yeah, right, "prevent" is the twin brother of "force". They have both
been expelled.

My crystal ball tells me that you want to have a form on a Web page so
that user input gets transferred into an Excel file on a server, right?
Then the first thing to consider is security. The second one is security.
The third one is... making sure nobody can crash your fine file by
submitting bogus data (say, a gigabyte of junk, or data that overwrites
existing data without proper authorisation). Forget E-mail here. It's
just an extra complication.

You'll need a form handler that checks the data, then manipulates an
Excel file somehow. It needs to use routines that operate on Excel's
internal data format. Good luck

--
Yucca, http://www.cs.tut.fi/~jkorpela/
Pages about Web authoring: http://www.cs.tut.fi/~jkorpela/www.html



Thu, 01 Jul 2004 08:03:34 GMT  
 Line breaks when pasting text / No line breaks in forms
As usual in Newsgroups there's the odd person who just can't help but be
rude. I have no idea why people are like this. I'll go through his rude
reply......

Quote:
> > I've searched everywhere - -

> Please try to get to the point a bit faster.

If I hadn't have written that, someone such as yourself who seems to just be
after an argument, would have accused me of not searching everywhere else
before posting the question! People just cannot win.

Quote:
> > I'm trying to paste data that I'm going to copy from email on a
> > regular basis, into Excel.
> So how does that relate to _HTML_? And what is the _original_ problem?

<snip>

If you would have read the COMPLETE post you would have found out exactly
what it has to do with HTML. Part of the problem came to me AS I was writing
to the other group. I didn't see sense in re-writing the post, as anyone
reading it properly would have understood.

Quote:
> > Well it was all working fine until I came across one
> > today with a line break in.
> Huh? You processed lots of E-mail before you encountered the first line
> break. Odd.

Why are you being so sarcastic? I never said I processed a lot of emails at
all. You're assuming that.

Quote:
> The technical side is under discussion elsewhere in this group right now,
> and you might take a look at some points:
> http://www.cs.tut.fi/~jkorpela/forms/textarea.html

A helpful comment at last! Thank you for that.

Quote:
> > I want to find a way that I can either put in a formula or macro in
> > Excel, to detect and delete line break.
> No, that's just your assumed technical approach to an unspecified _real_
> problem. I'm sure that Excel macros are not what you _really_ want, just
> an assumed tool... to _some_ end.

Rudeness once again. I stand by what I wrote. I cannot believe the way
you've critisied me at every turn.

Of course I appreciate the help that you DID include in your posting-But why
did you have to be so rude and sarcastic? Did I offend you in some way? I
simply asked a genuine question to the CORRECT newsgroup after doing as much
research as I could before posting it.

I usually find answers to problems just by searching groups. However, on
occasion I cannot find my answers so I post the question instead. Nearly
every time I do this, a small percentage of people have a go at me for
reasons I simply cannot understand. Your manner is totally unacceptable and,
to be blunt, confuses me! I wrote a polite and friendly posting because I
needed some help. Isn't that why these groups are here? I've been using the
Internet since before the World Wide Web even existed so I think I have
enough experience in these matters. I would have been most grateful to
anyone who replied to me whether it helped or not-But I will not put up with
postings like you submitted. It's a shame a few rude ignoramuses find it
necessary to spoil the atmosphere in the groups for everyone else.

In future, maybe if you do not wish to be polite, you shouldn't reply to
people at all? You acted like you own the newsgroup by such comments as,
"Please try to get to the point a bit faster". Am I causing disruption in
YOUR newsgroup maybe? I was under the impression this was a PUBLIC place?

Well I've had my say so I'll shut up. Apologies to others here who are
annoyed by this post but I hope you'll understand my anger when you read
what the Gentleman wrote.

Regards,
Geoff.



Sat, 03 Jul 2004 09:26:00 GMT  
 Line breaks when pasting text / No line breaks in forms
Rudeness and Trolling are the norm for this newsgroup (www*html). No, it's
not just Jukka, there a whole gang. I'm surprised none of them jumped on you
for critising him.

You were far too polite. When I get treated like that I tell them to {*filter*}
right off, so does a couple of others.


| As usual in Newsgroups there's the odd person who just can't help but be
| rude. I have no idea why people are like this. I'll go through his rude
| reply......
|
| > Please try to get to the point a bit faster.
|
| If I hadn't have written that, someone such as yourself who seems to just
be
| after an argument, would have accused me of not searching everywhere else
| before posting the question! People just cannot win.
|
| > So how does that relate to _HTML_? And what is the _original_ problem?
| <snip>
|
| If you would have read the COMPLETE post you would have found out exactly
| what it has to do with HTML. Part of the problem came to me AS I was
writing
| to the other group. I didn't see sense in re-writing the post, as anyone
| reading it properly would have understood.
|
| > Huh? You processed lots of E-mail before you encountered the first line
| > break. Odd.
|
| Why are you being so sarcastic? I never said I processed a lot of emails at
| all. You're assuming that.
|
| > The technical side is under discussion elsewhere in this group right now,
| > and you might take a look at some points:
| > http://www.*-*-*.com/ ~jkorpela/forms/textarea.html
|
| A helpful comment at last! Thank you for that.
|
| > No, that's just your assumed technical approach to an unspecified _real_
| > problem. I'm sure that Excel macros are not what you _really_ want, just
| > an assumed tool... to _some_ end.
|
| Rudeness once again. I stand by what I wrote. I cannot believe the way
| you've critisied me at every turn.
|
| Of course I appreciate the help that you DID include in your posting-But
why
| did you have to be so rude and sarcastic? Did I offend you in some way? I
| simply asked a genuine question to the CORRECT newsgroup after doing as
much
| research as I could before posting it.
|
| I usually find answers to problems just by searching groups. However, on
| occasion I cannot find my answers so I post the question instead. Nearly
| every time I do this, a small percentage of people have a go at me for
| reasons I simply cannot understand. Your manner is totally unacceptable
and,
| to be blunt, confuses me! I wrote a polite and friendly posting because I
| needed some help. Isn't that why these groups are here? I've been using the
| Internet since before the World Wide Web even existed so I think I have
| enough experience in these matters. I would have been most grateful to
| anyone who replied to me whether it helped or not-But I will not put up
with
| postings like you submitted. It's a shame a few rude ignoramuses find it
| necessary to spoil the atmosphere in the groups for everyone else.
|
| In future, maybe if you do not wish to be polite, you shouldn't reply to
| people at all? You acted like you own the newsgroup by such comments as,
| "Please try to get to the point a bit faster". Am I causing disruption in
| YOUR newsgroup maybe? I was under the impression this was a PUBLIC place?
|
| Well I've had my say so I'll shut up. Apologies to others here who are
| annoyed by this post but I hope you'll understand my anger when you read
| what the Gentleman wrote.
|



Sun, 04 Jul 2004 03:16:27 GMT  
 Line breaks when pasting text / No line breaks in forms

Quote:
> Rudeness and Trolling are the norm for this newsgroup (www*html). No, it's
> not just Jukka, there a whole gang. I'm surprised none of them jumped on
you
> for critising him.

> You were far too polite. When I get treated like that I tell them to {*filter*}
> right off, so does a couple of others.

Thanks for sticking up for me. Well deep down I'm feeling the same as you,
but being polite like I was makes them feel small and stupid. What they want
is a massive argument but if I keep cool and just give a fairly polite
response it'll just{*filter*}them off. That's probably why none of the 'gang'
had a go back at me. :)

Cheers,
Geoff.



Sun, 04 Jul 2004 05:04:45 GMT  
 Line breaks when pasting text / No line breaks in forms

Quote:

> Rudeness and Trolling are the norm for this newsgroup (www*html). No, it's
> not just Jukka, there a whole gang. I'm surprised none of them jumped on you
> for critising him.

trans: Mr Gisin considers the correction of factual inaccuracies to be an
infringement on his right of free speech

get back in the killfile...this time without a time limit

--
eric
"if at first you don't succeed,
then try again with it switched on"



Sun, 04 Jul 2004 19:05:08 GMT  
 Line breaks when pasting text / No line breaks in forms

Quote:
> > Rudeness and Trolling are the norm for this newsgroup (www*html). No,
it's
> > not just Jukka, there a whole gang. I'm surprised none of them jumped on
you
> > for critising him.

> trans: Mr Gisin considers the correction of factual inaccuracies to be an
> infringement on his right of free speech

> get back in the killfile...this time without a time limit

Hi Eric,

No offence to you of course, but I think he was just a bit annoyed at how
some people can't help themselves but be rude and sarcastic, and they come
across with the attitude that they own the group. There was nothing wrong
with what I wrote, and there weren't any factual inaccuracies because I made
it clear I didn't know the solution to my problem!

I know how that other guy feels about some people in newsgroups, but I
wouldn't have put it quite the same. :)

I'm not getting at you Eric-Just want to make that clear. I just want to
stick up for myself because I don't see what I've done wrong-It was someone
else who decided to have a moan at just about every sentence I wrote.

Cheers Eric,
Geoff.



Mon, 05 Jul 2004 01:11:33 GMT  
 
 [ 9 post ] 

 Relevant Pages 

1. Line break vs. text wrapping break

2. A97 - REPORT - PRINT 6 SIX, PAGE BREAK, PRINT 6 LINES, PAGE BREAK

3. also...once u change a page break by dragging the break line

4. Line Break vs. Paragraph Break

5. Line graph with break in line

6. Count Lines and Insert a Page Break at 50 Lines

7. Broken Lines in Line Graph

8. extra line breaks in plain text messages

9. Problem with Missing Line Breaks in Received Text Messages

10. Line Breaks in Text Box


 
Powered by phpBB® Forum Software © phpBB Group