Solved: Existing codes need help PLS

[Ken Puls]

Ken -- I'm back to thank you again for the great cake and wondering if I may ask for some frosting
I'd love to be able to "convert table to text" (I mean the range that
turns to a table after its copied and pasted to Word from Excel).

I know I've reached my limit ... so pls ignore if this asks too much of your time ...

Nee

Frosting? That was part of your original question, and I totally missed that the change I put in reverted back to the table pasting. My apologies!

Give this one a shot:

[vba]Sub Screenshot2()
'transfers XL range with format to Word doc
'makes changes to "c:\Test.doc" Change directory to suit
Dim Wdapp As Object, wdDoc As Object
With Sheets("sheet1").Range("A1:A100") 'change range to suit
With .Borders(xlInsideHorizontal)
.Weight = xlThin
.ColorIndex = 2
End With
With .Borders(xlEdgeTop)
.Weight = xlThin
.ColorIndex = 2
End With
With .Borders(xlEdgeLeft)
.Weight = xlThin
.ColorIndex = 2
End With
With .Borders(xlEdgeRight)
.Weight = xlThin
.ColorIndex = 2
End With
With .Borders(xlEdgeBottom)
.Weight = xlThin
.ColorIndex = 2
End With
.Copy
End With
On Error GoTo Errmsg
Set Wdapp = CreateObject("Word.Application")
Set wdDoc = Wdapp.documents.Add
With wdDoc
.Range(0).PasteSpecial DataType:=1
With .Parent
.Selection.Tables(1).Select
.Selection.Rows.ConvertToText Separator:=0, NestedTables:=True
.Selection.ParagraphFormat.Alignment = 0
End With
End With
Application.CutCopyMode = False
Wdapp.Visible = True
Set Wdapp = Nothing
Exit Sub
Errmsg: MsgBox "You have an error"
Wdapp.Quit
Set Wdapp = Nothing
End Sub[/vba]

Could probably be a little cleaner in the word side, but should do the trick.

[Rembo]

Hi guys,

At the risk of overlooking some things, why not use something along the lines of this:

[VBA]Sub Screenshot3()
'transfers XL range without format to Word doc
Dim Wdapp As Object, wdDoc As Document
Sheets(1).Range("A1:A100").Copy
On Error GoTo Errmsg
Set Wdapp = CreateObject("Word.Application")
Set wdDoc = Wdapp.documents.Add
wdDoc.Content.PasteSpecial Link:=False, DataType:=wdPasteText, Placement:= _
wdInLine, DisplayAsIcon:=False
Wdapp.Visible = True
Application.CutCopyMode = False
Set Wdapp = Nothing
Exit Sub
Errmsg: MsgBox "You have an error"
Wdapp.Quit
Set Wdapp = Nothing
End Sub[/VBA]

What's the point pasting your Excel data into a table in a Word document when you'll be deleting the table afterwards right?
I had to set a reference to the Microsoft Word 9.0 Object Library (9.0 is the highest number on my computer for that library) to make it work.

Regards,

Rembo

[joelle]

Hello Gentlemen,

So thankful for the help from you all.

Ken: I'm so appreciative of your generosity / patience in donating the one set of codes after another!

Remco: Quite a while! thanks a bunch too for helping me with the codes.

They all work nicely!

Warm regards,
Nee

[Ken Puls]

Hi guys,

At the risk of overlooking some things, why not use something along the lines of ...

What's the point pasting your Excel data into a table in a Word document when you'll be deleting the table afterwards right?
I had to set a reference to the Microsoft Word 9.0 Object Library (9.0 is the highest number on my computer for that library) to make it work.

Heya Remco!

Actually, I think my first version should have done that. The deal was though, that while it lost the table, it also lost the formatting. (I used a late bind, hence the PasteSpecial = 0 instead of PasteAsText.

Not saying at all that mine is the right way, but I was able to make it work with keeping formats and removing the table.

[Rembo]

Hi Ken and Nee,

Remco: Quite a while! thanks a bunch too for helping me with the codes.

Yes, it's been a little while. Every now and then I surface to see what's new. This is quite a forum;there are some very skilfull people at work here.

..but I was able to make it work with keeping formats and removing the table.

Yup, that's a pretty crafty solution you scribbled down I also stored it on my computer for reference. The solution I presented ignores the formate completely.