PDA

View Full Version : [SOLVED] Preserve line breaks when inserting/pasting into a single Excel cell



lebowski
04-08-2015, 07:11 AM
Hi, I have trawled through the web for answers to what feels like the most basic issue but cannot find a solution. I am trying to insert multi-line content from Word into an individual cell in excel. I have tried two methods:

Method 1: From Word VBA script, pass content (from text paragraphs in Word) to string and insert into the desired Excel cell. The line breaks are lost despite appearing in string when displayed in a Msgbox. (Since all the text is is one string, I cannot concatenate multiple strings with line break characters/code.)


Method 2: From Word VBA script, add content (from text paragraphs in Word) to the clipboard and paste into the desired Excel cell. The text gets spread across multiple rows (i.e. Excel sees each line break as a new cell).

Whilst manually, I can choose to select a cell in a worksheet and paste across multiple cells or choose to enter the cell and paste within the cell only (which is what I need to do in code!). I cannot see how the latter can be executed in VBA, as the paste option appears to apply to the worksheet only:


'Pastes across multiple rows as defined by line breaks
XL_App.Sheets(1).Paste

'Attempted code that does not work
XL_App.Sheets(1).Range("A1").Paste

'Attempted code that does not work
XL_App.Sheets(1).Cells(1, 1).Paste

Can anyone please help?

mperrah
04-08-2015, 01:45 PM
Not sure if this helps, but when you paste multiple rows of text Excel puts it into a cell as one line by default.
There is an option to wrap text, but this will vary results based on the width of the cell.

So I played with a paragraph in Word, copied into Excel,
found that sizing the cell width in Excel gave the appearance of the paragraph copied from word.
If this is what you're going for,
start the macro recorder:
copy from Word into Excel and wrap text
then resize the cell to your liking and take note of those settings.

I got this for having wrap text on.

ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:= _
False
With Selection
.WrapText = True ' see here
.Orientation = 0
.AddIndent = False
.IndentLevel = -1
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

and this for sizing column B

Columns("B:B").ColumnWidth = 100

Also tried opening the cell to edit and pasted there.
It put all the text into one cell, but added it as a cell formula.
I noted that for each line feed there was a:

" & Chr(10) & "
added to the text. This will alter how the text looks as you change the cell width.
not sure how much text you need to manipulate, but maybe some of these observations will help.

-mark

lebowski
04-09-2015, 12:38 AM
Thanks for the suggestions. Tried without success unfortunately. I have text wrapping selected on the cell already and the paste code again pasted into multiple rows. I believe this is because when pasting on the worksheet, by default Excel will see line breaks as new rows. There has to be a way to paste multiple line content into a single cell in VBA. Like I say, this can be done manually when pasting into the cell itself (i.e. by double clicking the cell to change to blinker or pasting into the cell content address bar).

lebowski
04-09-2015, 02:21 AM
The reason I needed line breaks in cells was to allow me to then split the cells into rows. I got around this issue by a long-winded method of replacing the Chr(13) with holder text, then using the holder text in Excel to create new rows.

Yongle
04-09-2015, 02:45 AM
This workaround will work for you. Basically it swaps out the line break character in Word and replaces it with the one Excel requires.
Word refused to play ball and I could not get it to do the complete replacement - it doesn't seen to like special Char(10) which is the the one we need in Excel! So for the moment the job is done in 2 parts, replace special Char(13) in Word with the string xxx, and then replace string xxx in Excel with special Char(10).

What you need to do to try it out is:
1 Confirm which character WORD is using for your line break . I suspect it will be ^p and you can test this by opening your WORD document and searching for ^p (using search and replace) and it should take you to the first line break
2 Then use "search and replace" in WORD and replace the relevant incidences of ^p with xxx
(you may need to do this in blocks, otherwise all your text will end up in one cell!)
3 Paste the amended text into cell A1 in EXCEL
4 Either use the formula =SUBSTITUTE(A1,"xxx",CHAR(10)) in cell B1 (ensure that WrapText is selected for B2) or you could use the macro below adapted to your needs.
NOTE xxx is case sensitive
Char(10) in Excel is equivalent of Chr(10) in vba
Replace function in VBA is the eqivalent of the Substitute function in Excel

[
Sub LineBreak()

Dim str1 As String, str2 As String
str1 = Range("A1").Value
str2 = Replace(str1, "xxx", Chr(10))
Range("B1").Value = str2

End Sub

jonh
04-09-2015, 06:48 AM
Public Sub Paster()
With New MSForms.DataObject
.GetFromClipboard
ActiveCell.Value = .GetText
End With
End Sub

(Add a userform to create the msforms reference.)

lebowski
04-10-2015, 12:11 AM
Many thanks, will try this.


This workaround will work for you. Basically it swaps out the line break character in Word and replaces it with the one Excel requires.
Word refused to play ball and I could not get it to do the complete replacement - it doesn't seen to like special Char(10) which is the the one we need in Excel! So for the moment the job is done in 2 parts, replace special Char(13) in Word with the string xxx, and then replace string xxx in Excel with special Char(10).

What you need to do to try it out is:
1 Confirm which character WORD is using for your line break . I suspect it will be ^p and you can test this by opening your WORD document and searching for ^p (using search and replace) and it should take you to the first line break
2 Then use "search and replace" in WORD and replace the relevant incidences of ^p with xxx
(you may need to do this in blocks, otherwise all your text will end up in one cell!)
3 Paste the amended text into cell A1 in EXCEL
4 Either use the formula =SUBSTITUTE(A1,"xxx",CHAR(10)) in cell B1 (ensure that WrapText is selected for B2) or you could use the macro below adapted to your needs.
NOTE xxx is case sensitive
Char(10) in Excel is equivalent of Chr(10) in vba
Replace function in VBA is the eqivalent of the Substitute function in Excel

[
Sub LineBreak()

Dim str1 As String, str2 As String
str1 = Range("A1").Value
str2 = Replace(str1, "xxx", Chr(10))
Range("B1").Value = str2

End Sub

dukebdevilsr
05-07-2015, 09:17 AM
I have searched high and low on these boards to answer a very simple macro and this seems to be the closest thing to what I am going for. I am definitely a beginner when it comes to VBA but I think if I had the WHOLE script from the original post on this, I could certainly figure it out.

Problem Statement
I need to ctrl+A ctrl+C from a word document and paste it into A1 sheet1! on ABC.xlsx. That is it and that is all. I think my question is so basic that everyone on these boards is under the assumption that this is a known skill (dunce cap on in the corner)...