Printing Excel Data into a MSWord document as formatted text, not a table.

[nbonanno]

My boss is being difficult. Using some code I found on this site, I can have our status report Excel spreadsheet create a lovely MSWord document...but the data is in table format. She wants plain ole' formatted text. When I use " appWD.Selection.Pastespecial datatype:=wdpastetext" it comes over without bolds or underlines. I also tried wdpasteRTF but that seemed to give me a table again.

Is there someway to have it copied into the MSWord document "prettily" but not be in a table? Thank you in advance.

Here is my code:

<code>Sub Create_msword_for_Kia_Report()
Dim appWD As Word.Application
Set appWD = CreateObject("Word.Application.12")
appWD.Visible = True
appWD.Documents.Add

Sheets("For Kia's Report").Select
Range("a1:g9").Copy
appWD.Selection.PasteSpecial DataType:=wdPasteText
appWD.ActiveDocument.SaveAs
Filename:="C:\Users\nb7149\Documents\Issuetrak\statusreport.doc"
appWD.ActiveDocument.Close
appWD.Quit
End Sub

</code>

 

[sijpie]

There are basically three ways of pasting:

• As a table (with or without formatting)
• As a picture (not editable, still shows table format)
• As plain text, no formatting and cells become unalligned, so column format totally lost.

there are no other ways.

So if you want as plain text, all column formatting gone (ie text no longer alligned to collumns) but with bolds & underlines, then you will have to process the text cell by cell. But I doubt she will like the output.

Suggest that you do it once by hand: paste special, Keep text only, then apply the bolding and underlining. And show it to her together with the one you made earlier.

Then if she still prefers the new one, you will have to paste each cell, read the formatting in the cell and apply it on the pasted text.

 

[cytop]

Also: Using VBA to print to a MSWord document, with formatting but without a table -

 

[nbonanno]

Very clear and concise. Thank you. I'll try your suggestion.

If I do process it cell by cell, how do I read the formatting of a cell and apply it in the msword doc using VBA? And can I add tabs?

Currently, the spreadsheet is pretty simple - I created a sheet that would look just like the report. I'm not sure how to attach a spreadsheet here, but it would look something like this...
Here are the first 3 columns. Anything that is long is simply not text wrapped...its just three columns.

PS2000 Communication
ProdBuild: Generate and distribute on 7/22/2013
PSS Spotlight: None this time.
BWD Evolves: None this time.
BPub Evolves: BPub Evolves: Create Article PDFs from Tumbleweed-7/19/2013

Thanks for your help.

 

[sijpie]

What cytop refers to is that if you post on two forums, please mention that in both posts, else possibly a lot of unrequired work is being done.

So your table looks like

Excel 2010
	A	B	C
12	PS2000 Communication
13	ProdBuild:	Generate and distribute on	7/22/2015
14	PSS Spotlight:	None this time.
15	BWD Evolves:	None this time.
16	BPub Evolves:	Create Article PDFs from Tumbleweed-	7/19/2015
MyComp2

yes?

 

[nbonanno]

Yes, I didn't realize I was suppose to point out the double-post...I will certainly do so in the future. And yes...that is just what my current table looks like.

What cytop refers to is that if you post on two forums, please mention that in both posts, else possibly a lot of unrequired work is being done.

So your table looks like
Excel 2010

	A	B	C
PS2000 Communication
ProdBuild:	Generate and distribute on	7/22/2015
PSS Spotlight:	None this time.
BWD Evolves:	None this time.
BPub Evolves:	Create Article PDFs from Tumbleweed-	7/19/2015

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

</tbody>

MyComp2​

yes?

 

[sijpie]

I just thought, how about converting a table in word to text? This option is still there. I recorded it in a macro and pasted it inot your original macro (with a few minor changes. See if this works:


<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Create_msword_for_Kia_Report()<br>    <SPAN style="color:#00007F">Dim</SPAN> appWD <SPAN style="color:#00007F">As</SPAN> Word.Application<br>    <SPAN style="color:#00007F">Dim</SPAN> sFN <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, sP <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    sP = "C:\Users\nb7149\Documents\Issuetrak\"<br>    sFN = "statusreport.doc"<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> appWD = CreateObject("Word.Application.12")<br>    appWD.Visible = <SPAN style="color:#00007F">True</SPAN><br>    appWD.Documents.Add<br>    <br>    Sheets("For Kia's Report").Select<br>    Range("a1:g9").Copy<br>    <br>    <SPAN style="color:#007F00">' Paste in word as Excel table keep original format</SPAN><br>    appWD.Selection.PasteExcelTable <SPAN style="color:#00007F">False</SPAN>, <SPAN style="color:#00007F">False</SPAN>, <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#007F00">' move back into table and convert to text with tabs</SPAN><br>    appWD.Selection.MoveUp Unit:=wdLine, Count:=2<br>    appWD.Selection.Rows.ConvertToText Separator:=wdSeparateByTabs, _<br>            NestedTables:=<SPAN style="color:#00007F">True</SPAN><br>    <br>    appWD.ActiveDocument.SaveAs Filename:=sP & sFN<br>    appWD.ActiveDocument.Close<br>    appWD.Quit<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[nbonanno]

ooOOoooh....that could do the trick. Let me try it and I'll let you know! Thanks.

I just thought, how about converting a table in word to text? This option is still there. I recorded it in a macro and pasted it inot your original macro (with a few minor changes. See if this works:



Sub Create_msword_for_Kia_Report()
****Dim appWD As Word.Application
****Dim sFN As String, sP As String
****
****sP = "C:\Users\nb7149\Documents\Issuetrak\"
****sFN = "statusreport.doc"
****
****Set appWD = CreateObject("Word.Application.12")
****appWD.Visible = True
****appWD.Documents.Add
****
****Sheets("For Kia's Report").Select
****Range("a1:g9").Copy
****
****' Paste in word as Excel table keep original format
****appWD.Selection.PasteExcelTable False, False, False
****' move back into table and convert to text with tabs
****appWD.Selection.MoveUp Unit:=wdLine, Count:=2
****appWD.Selection.Rows.ConvertToText Separator:=wdSeparateByTabs, _
************NestedTables:=True
****
****appWD.ActiveDocument.SaveAs Filename:=sP & sFN
****appWD.ActiveDocument.Close
****appWD.Quit
End Sub

 

[nbonanno]

It works GREAT! Thank you!
I need to fiddle with it a little. Sometimes I get an error...something about the "remote server" not being available which I think is something like it doesn't like it if the file already exists, or if MSWord is already open...not sure yet.

And the bolding format comes over which is wonderful...but it doesn't look like the underline formatting I have in the Excel sheet is coming over to MSWord. A small matter I can deal with, but is there some way to have it bring the underlined words in a cell?

Thank you, thank you again for your help.

I just thought, how about converting a table in word to text? This option is still there. I recorded it in a macro and pasted it inot your original macro (with a few minor changes. See if this works:



Sub Create_msword_for_Kia_Report()
****Dim appWD As Word.Application
****Dim sFN As String, sP As String
****
****sP = "C:\Users\nb7149\Documents\Issuetrak\"
****sFN = "statusreport.doc"
****
****Set appWD = CreateObject("Word.Application.12")
****appWD.Visible = True
****appWD.Documents.Add
****
****Sheets("For Kia's Report").Select
****Range("a1:g9").Copy
****
****' Paste in word as Excel table keep original format
****appWD.Selection.PasteExcelTable False, False, False
****' move back into table and convert to text with tabs
****appWD.Selection.MoveUp Unit:=wdLine, Count:=2
****appWD.Selection.Rows.ConvertToText Separator:=wdSeparateByTabs, _
************NestedTables:=True
****
****appWD.ActiveDocument.SaveAs Filename:=sP & sFN
****appWD.ActiveDocument.Close
****appWD.Quit
End Sub

 

[sijpie]

Don't know. I can't replay the macro because I get an error when assigning the appWD .

But if I do it manually, all the formatting is kept.

You could also put the last parameter in

    appWD.Selection.PasteExcelTable False, False, False

to True

    appWD.Selection.PasteExcelTable False, False, True

which pastes it as RTF, see if that helps