Paste contents of Word document into single Excel cell

[Thingol]

Hi All,

I'm trying to write a macro in Excel that pastes the text of several rtf-documents into a single Excel cell for each of the documents. What I have already is a macro that checks what rtf-files are available in a certain folder, and that copies the content of the rtf-file. It also pastes the copied content into the assigned cell. So, what is the problem then? Well, the rtf-documents contain several line-breaks. When pasting in Excel, these line-breaks make the pasted text occupy several cells, instead of just one.

Therefore, my question is: Is it possible to paste the contents of a textfile like the rtf-document into a single Excel cell and make sure that the linebreaks appear within the cell, as if they would look when using alt+enter? If I just know whether that is possible at all, I will also be able to put it into the macro, I think.

Thanks a lot in advance for any help!

Best regards,
Martijn Senden.

In the Beginning there was nothing, which exploded.

--Terry Pratchett, Lords and Ladies--

 

[SkipVought]

Hi,

Got to menu item Data/Text to Columns and in the DELIMITED option, UNCHECK the boxes that are checked.

Then do your paste.

Alternatively, EDIT the cell and paste while in EDIT.


Skip,
[sub]
[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE[/sub]

 

[Thingol]

Hi Skip,

Thanks! Ok, I thought I would be able to record a macro of this procedure and then include the relevant code in my own macro, but I can't record the paste inside the cell!

So: how would I go about pasting the word content into the cell directly from my macro?

Thanks again!

Best regards,
Martijn Senden.

In the Beginning there was nothing, which exploded.

--Terry Pratchett, Lords and Ladies--

 

[SkipVought]

Did you try the FIRST suggestion?


Skip,
[sub]
[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE[/sub]

 

[Thingol]

Yes, I did. It didn't work out (I probably did something wrong). I got an error message:

(translated from Dutch, so it may not be exact):

No data was selected to divide across the columns.




Best regards,
Martijn Senden.

In the Beginning there was nothing, which exploded.

--Terry Pratchett, Lords and Ladies--

 

[SkipVought]

Well you have to have SOME data in a cell in order to do it!


Skip,
[sub]
[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE[/sub]

 

[AnotherHiggins]

Martijn, when you paste from Word, the text is automatically split into different cells, without your using Edit > Text To Columns, right?

Is your data being split into multiple columns on the same ROW (splitting left to right), or is it being split into multiple rows the same COLUMN (splitting up and down)?

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[BenRowe]

The only way I know to do this would be to do a global in Word and replace any line breaks or para marks with a symbol (I usually use §) and then do a global in Excel to replace this symbol with Chr(10).

 

[Thingol]

Thanks all!

Skip: Yes, that's the point: I don't since I'm pasting stuff from word and I want all of the content to end up in the same cell.

John: The content is being split into several rows (one column).

Ben: That seems to be heading towards a solution for my problem. How would I replace line breaks with a specific symbol in Word and then replace these symbols with chr(10) in Excel?

Best regards,
Martijn.

In the Beginning there was nothing, which exploded.

--Terry Pratchett, Lords and Ladies--

 

[SkipVought]

And what is the REASON for having the entire text in one cell?

How would haveing the text in multiple cells in the same column be a problem?


Skip,
[sub]
[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE[/sub]

 

[BenRowe]

If they are line breaks, you would search for ^l (lower case alpha l). If they are para marks, you would search for ^p. As far as creating the macro in Excel, you can just create one and then paste the following into it:

Cells.Replace What:="§", Replacement:=Chr(10), LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

This assumes you are using § as your symbol. Of course, if you think there is a likelihood this symbol would be in your documents, you'll need to come up with something else.

 

[Thingol]

Hi again!

Thanks once more!

Skip: I know, it's probably an unusual thing to want to do. I'm a project manager and for one of my projects a webdesigning agency is creating a website. The website is going to have a database with publications in it. To fill the database the designer asked me to provide him with an Excelsheet that has several fields for each publication. One of the fields is going to be for the abstracts of the publications. Each abstract should be contained in one cell, since I want records to be on one line. All the abstracts are in separate .rtf-files. I created a macro to copy the content from these .rtf-files into the Excelsheet.
Now that I'm writing this I start thinking I should probably have been making this macro in Access and then exporting the created table to Excel. That would have probably saved me all this trouble.

Ben Rowe: Thank, I'll try that, it seems to do what I need. However: the .rtf files do not contain any symbol yet for the line-breaks. How would I replace the line breaks with §-symbols using the macro? If I have to do this by hand I would have to do it for each .rtf file, and that would be a real hassle.

Thanks again guys! You are really helping me out!

Best regards,
Martijn Senden.

In the Beginning there was nothing, which exploded.

--Terry Pratchett, Lords and Ladies--