Solved

Copy word table, paste into excel, simple?

Posted on 2004-04-30
13
5,036 Views
Last Modified: 2012-05-04
Hey guys and gals,

Here's my problem, one of my partners created this 75 page MS Word table. Each cell has a blob of text with hard returns in it. I tried to copy the word table and paste it into excel, but wherever there is a hard return, the text goes to the next cell in excel and the formatting gets screwed up. Here's a list of stuff I already tried with no success:

1. Tried converting it to tab delimited and then importing to excel.
2. Copied whole table and pasted into excel
3. Downloaded a program called Word2Excel Converter
4.  Did the paste special thing in excel and chose "Keep original formatting"
None of these things worked...

Is there a VBA program or something out there that I can use? Any other ideas/suggestions are welcome, I am pulling my hair out trying to figure this out...

Thanks everyone!

-Steve
0
Comment
Question by:Venturer58
13 Comments
 
LVL 16

Expert Comment

by:sebastienm
ID: 10965363
Hi Steve,
1. Should these hard Returns be considered in Excel as:
   - word wrap within a single cell
   -or real enter within a single cell
   ?
2. Do you have a visible border around each cell in the Word table? or not always? When pasting top excel, hard returns becomes separate cells, but these cells dont have a top-horizontal border, they could therefore be tracked that way in a macro.

Regards,
Sebastien
 
0
 
LVL 1

Author Comment

by:Venturer58
ID: 10965421
I would like the hard returns to transfer to a hard return within a single cell. For example:

_____
BLAH |
        |
BLAH |
_____|

in one cell in the MS Word table to:

_____
BLAH |
        |
BLAH |
_____|

in the excel cell.

Does this answer your question?
0
 
LVL 24

Accepted Solution

by:
R_Rajesh earned 250 total points
ID: 10966060
Steve,

execute this form your word document. The code selects the first table in the document, replaces all carrage returns with the text "myCreturn" then copies the table over to xl and then replaces all "myCreturn" back to carrage return.

----------------------
Sub wrdTOxl()
Dim xlApp As Object, xlSht As Object, myTbl As Table
Set myTbl = ActiveDocument.Tables(1): myTbl.Select
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "^p": .Replacement.Text = "myCreturn"
.Forward = True: .Wrap = 0
End With
Selection.Find.Execute Replace:=wdReplaceAll
Set xlApp = CreateObject("Excel.Application"): xlApp.Visible = True
Set xlSht = xlApp.Workbooks.Add.Sheets(1)
myTbl.Range.Copy: xlSht.Paste
xlSht.UsedRange.Replace "myCreturn", vbCrLf, 2
Set myTbl = Nothing: Set xlSht = Nothing: Set xlApp = Nothing
End Sub
---------------------


Rajesh
0
 
LVL 24

Expert Comment

by:R_Rajesh
ID: 10966394
if the carriage return?linefeed combination creates strange chars in xl you could replace it with only a linefeed

xlSht.UsedRange.Replace "myCreturn", vbLf, 2
0
 
LVL 37

Expert Comment

by:Joanne M. Orzech
ID: 10967614
Search and replace hard return ^p with a space?  That's what I've done in the past.
0
 
LVL 1

Author Comment

by:Venturer58
ID: 10972019
Okay guys, we're almost there, I used your code above and got everything into excel almost correctly, but it gave the error msg "Formula too long" and instead of carraige returns, it inserted "myCreturn" where the hard returns should be... any way to replace "myCreturn" with a carraige return? If so, we're good to go...

Thanks!!

-Steve
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 24

Expert Comment

by:R_Rajesh
ID: 10972134
Steve,

how long is the biggest string in the word table???

anyway try this modified code:

Sub wrdTOxl()
Dim xlApp As Excel.Object, xlSht As Excel.Object, myTbl As Table
Set myTbl = ActiveDocument.Tables(1): myTbl.Select
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.text = "^p": .Replacement.text = "myCreturn"
.Forward = True: .Wrap = 0
End With
Selection.Find.Execute Replace:=wdReplaceAll
Set xlApp = CreateObject("Excel.Application"): xlApp.Visible = True
Set xlSht = xlApp.Workbooks.Add.Sheets(1)
myTbl.Range.Copy
xlSht.Cells.NumberFormat = "@"
xlSht.Paste
xlSht.Cells.NumberFormat = "@"
'On Error Resume Next
xlSht.UsedRange.Replace "myCreturn", vbLf, 2
'On Error GoTo 0
Set myTbl = Nothing: Set xlSht = Nothing: Set xlApp = Nothing
End Sub


if you still get the error, run this sub form xl once the table has been transfered there.

Sub test1()
Cells.Replace What:="myCreturn", Replacement:=vbLf, LookAt:=xlPart
End Sub


Rajesh
0
 
LVL 24

Expert Comment

by:R_Rajesh
ID: 10972140
oops... the second line in the above code should be this:
Dim xlApp As Object, xlSht As Object, myTbl As Table

R
0
 
LVL 1

Author Comment

by:Venturer58
ID: 10972329
almost worked... alright, I ran your very first block of code and that was great, everything transferred formatted as I needed it, except "myCReturn" was where the carraige returns should have been, so what I did was do a Find/Replace all "myCreturn" with a carraige return, and that worked for every cell, except the cells (about 50 cells) that had really long text strings in them. I got the error "Formula too long" message when trying to do a find/replace into these cell. I tried reformatting the cells to "Text", but this just changed my text fields to "####" and when i went to make the field wider all I saw was more # signs.

I ran your second block of code and I got # signs for all of my fields except a few... So I am now manually finding and replacing every "myCreturn" in the cells that have a ton of text in them... I tried to run this:

Sub test1()
Cells.Replace What:="myCreturn", Replacement:=vbLf, LookAt:=xlPart
End Sub

but it didn't seem to do anything...

Is there a way in excel to find all of the myCreturn's and replace them with carraige returns? Thanks so much!

-Steve
0
 
LVL 24

Expert Comment

by:R_Rajesh
ID: 10972389
Hi steve,
sorry, I am going out for couple of hours...
Any possibility of me getting a chance to look at you word doc. Email id is in my profile

R
0
 
LVL 1

Author Comment

by:Venturer58
ID: 10972637
I apologize, I can't e-mail it to you, it's corporate confidential information... I work for a consulting firm and the document is an audit program for one of our clients.

I will continue to manually find and replace the myCreturn's and if you have any other ideas, please post them. If I finish over the next couple of hours, i'll accept your (R. Rajesh) comment as the answer, you saved me hours worth of work already. Thanks again!

-Steve
0
 
LVL 1

Author Comment

by:Venturer58
ID: 10972698
Got it, whohooo!
0
 
LVL 24

Expert Comment

by:R_Rajesh
ID: 10987253
hi steve,
sorry we couldn't nail the replacing bit.
thanks for the grade :)
R
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Some time ago I was asked to create a VBA function that would calculate a check digit for an input number, using the following procedure: First, sum up all the individual digits in the number If that sum value has more than one digit, then sum up …
PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now