Solved: Inserting Word Tables from XL

[Dave]

I'm missing abit of learnin about how tables are inserted in a Word doc and probably also some stuff about characters. I've been messing around with some code that uses XL to open a Word doc and then copy some XL ranges to Word tables. The contents of the .doc are created by XL which includes adding one cell tables to the .doc as placeholders for the XL range to be copied (as picture). Thus far it seems tables are inserted at the specified range which is a character location. An empty .doc has 1 hidden char (end of doc char?) The following code works fine but I just can't figure out why? or more specifically why the created .doc never has any blank line after the TableTest text (before each table) and then has the blank line after each table. Sounds confusing but if you look at the code, the TableTest text is added, then a .typeparagraph is added, the range is set based on the current character count, and then the table is inserted at that range. Why does the blank line follow all of the tables instead of preceeding it? Why does a table have 3 characters? You need a test.doc and some XL data from A1:L4 to trial the code. Any help will be greatly appreciated. Dave

Public Sub XLRngToWordTable()
Dim oWDBasic As Object, MyRange As Variant, Txtstr As String
Dim wrdDoc As Object, Rng As Range, Cnt As Integer
'makes 1 cell Word Table(s) and adds XL range(s) to table(s)
'rngs a1:c4; d1:f4; g1:i4; and j1:l4 in this eg.
'no Word reference required

On Error GoTo ErFix
Set oWDBasic = CreateObject("Word.Application")
'.doc MUST exist ie. change as needed
Set wrdDoc = oWDBasic.Documents.Open(Filename:="D:\test.doc")

'clear doc
With wrdDoc
.Range(0, .Characters.Count).Delete
End With
'MsgBox "Empty Doc Char Cnt: " & wrdDoc.Characters.Count
For Cnt = 1 To 4 '# of ranges/tables
'add text to top of table and space after table
With oWDBasic.Selection
.typetext Text:="***TableTest***" '(15chars)
.typeparagraph 'add paragraph for space between tables (1char)
'end of doc char(hidden) (1char)
'loop adds 3 chars for table and 17 for above (ie loop adds 20 chars)
'insert @ 15, 35, 55, 75
End With
'MsgBox "Total Char Cnt: " & wrdDoc.Characters.Count
'To add additional blank lines following each table:
'add additional .typeparagraph above AND
' wrdDoc.Characters.Count - 2 changes to wrdDoc.Characters.Count - 3
' and so on for each additional blank line.
Set MyRange = oWDBasic.activedocument.Range _
(Start:=wrdDoc.Characters.Count - 2, _
End:=wrdDoc.Characters.Count - 2)
'MsgBox "Table insert at Char: " & wrdDoc.Characters.Count - 2
'add one cell table (adds 3 chars)
With wrdDoc
.tables.Add Range:=MyRange, NumRows:=1, NumColumns:=1
End With
'set XL rng to copy
With Sheets("Sheet1")
Select Case Cnt
Case 1: Set Rng = .Range(.Cells(1, "A"), .Cells(4, "C"))
Case 2: Set Rng = .Range(.Cells(1, "D"), .Cells(4, "F"))
Case 3: Set Rng = .Range(.Cells(1, "G"), .Cells(4, "I"))
Case 4: Set Rng = .Range(.Cells(1, "J"), .Cells(4, "L"))
End Select
End With
Rng.CopyPicture Appearance:=xlScreen, Format:=xlPicture
'paste range to table cell (1,1)
With oWDBasic.activedocument.tables(Cnt).Cell(1, 1).Range
.Paste
End With

'fit table to range
With oWDBasic.activedocument.tables(Cnt)
.Columns.AutoFit
End With
Application.CutCopyMode = False
Next Cnt

'add text string after table
Txtstr = "Test finished at: " & Now()
With oWDBasic.activedocument
.content.InsertAfter Txtstr
End With

'close and save doc
oWDBasic.activedocument.Close savechanges:=True
Set wrdDoc = Nothing
oWDBasic.Quit
Set oWDBasic = Nothing
MsgBox "Finished"
Exit Sub

ErFix:
On Error GoTo 0
MsgBox "File error fix"
Set wrdDoc = Nothing
oWDBasic.Quit
Set oWDBasic = Nothing
End Sub

[macropod]

Hi Dave,

An 'empty' document contains a paragraph mark, which accounts for the character you reported. It's more a 'non-printing' character than a hidden one.

A table has a minimum of two characters per cell (one being equivalent to a paragraph break, the other being the end-of-cell marker), plus one character per row, so a single-celled table has 3 characters.

In any Word document, there must be a paragraph after the last table. The paragraph concerned may or may not contain any text.

I haven't look at your code in detail, but the above should help explain what's going on.

[Dave]

Many thanks macropod. That is very useful information. I hope a few follow-up questions are okay. Does the non-printing character persist after contents are added to the doc and if so where (ie. floats at the end of the doc contents?). If so, does this character qualify as a paragraph after the last table? Again, thank you for your time. Dave

[fumei]

Basically, a re-iteration of macropod's comments...

"Does the non-printing character persist after contents are added to the doc and if so where (ie. floats at the end of the doc contents?)."

There is ALWAYS at least one paragraph mark in a Word document.

There is ALWAYS a paragraph mark after the last table. Although technically speaking, that could be stated as that there is always a paragraph mark after ANY table. As a table in the middle of a document has a paragraph mark following it, yes? But in any case, you can not ever have atable as the last paragraph in a Word document. Ever.

Understanding paragraph marks is, IMO, the most important object in Word.

I would suggest keeping Show/Hide set as ON. I know it takes a little getting used to, but it is very useful to be able to see those paragraph (and those darn end-of-cell, end-of-row) marks.

macropod: "so a single-celled table has 3 characters."

Well....not really. A single cell table:
Sub CountEmDano()
Dim r As Range
Set r = Selection.Tables(1).Cell(1, 1).Range
MsgBox r.ComputeStatistics(wdStatisticCharacters)
MsgBox Asc(r.Text)
MsgBox Asc(Right(r.Text, 1))
End Sub


messagebox_1: 0 (no characters)
messagebox_2: 13 ASCII character of "first character"
messagebox_3: 7 ASCII character of "second character"

The end-of-cell (and end-of-row) marker is, AFAIK, the only instance of a multiple ASCII character. It is both - in some weird Microsoft way - 13 and 7.

"If so, does this character qualify as a paragraph after the last table? "

Yes. it not only qualifies, it IS a paragraph.

[macropod]

The end-of-cell (and end-of-row) marker is, AFAIK, the only instance of a multiple ASCII character. It is both - in some weird Microsoft way - 13 and 7.
Au contraire, if you run your code on a one-row two-celled table, you'll get the same result, even though you're only testing the first cell!

Actually, I was in error for a completely different reason - the end-of-row marker consists of two characters also. You can test the single cell with:

Sub Demo()
Dim i As Integer, x As String
x = Selection.Tables(1).Cell(1, 1).Range.Text
For i = 1 To Len(x)
MsgBox Asc(Mid(x, i, 1))
Next
End Suband, by deleting '.Cell(1, 1)', you can test the whole row. For a single-celled table, the latter version will return 13, 7, 13 and 7 via the message box.

[Dave]

For funzies, and probably a bit of a diversion, trial my code. It yields some output that might add to the discussion.

wrdDoc.Characters.Count - 1

craps out... and it shouldn't when you remove the .typeparagraph. If you remove the .typeparagraph (and leave the rest alone), there is no spacing between tables. I still don't get why it seems that within the loop you can't add a blank line between the TableTest text and the table, you can only add blank lines beneathe the tables. I get that the insertion point of the first table (etc.) actually creates the blank lines beneathe each table (ie. the end of doc text adds the text beneathe the last table at the same spacing as the tables even though no spacing has been added). Why is it that you can't just type text, add a blank line (ie.typeparagraph), and add the table? Just set the range after the .typeparagraph instead of before it ... doesn't work? Please continue your informative discussion. I'm certainly learning lots from it. Thanks. Dave

[macropod]

Hi Dave,

Here's a simplification of your code:Public Sub XLRngToWordTable()
Dim ObjWd As Object, MyRange As Variant, Txtstr As String
Dim wrdDoc As Object, Rng As Range, Cnt As Integer
'makes 1 cell Word Table(s) and adds XL range(s) to table(s)
'rngs a1:c4; d1:f4; g1:i4; and j1:l4 in this eg.
'no Word reference required

On Error GoTo ErFix
Set ObjWd = CreateObject("Word.Application")
'.doc MUST exist ie. change as needed
Set wrdDoc = ObjWd.Documents.Open(Filename:="D:\test.doc")

'clear doc
wrdDoc.Range.Delete
'MsgBox "Empty Doc Char Cnt: " & wrdDoc.Characters.Count
For Cnt = 1 To 4 '# of ranges/tables
'add text to top of table and space after table
With wrdDoc
.Content.Insertafter "***TableTest***" & vbCr '(16chars)
'MsgBox "Total Char Cnt: " & wrdDoc.Characters.Count
Set MyRange = .Range.Characters.Last
'add one cell table (adds 3 chars)
.tables.Add Range:=MyRange, NumRows:=1, NumColumns:=1
'set XL rng to copy
With Sheets("Sheet1")
Select Case Cnt
Case 1: Set Rng = .Range(.Cells(1, "A"), .Cells(4, "C"))
Case 2: Set Rng = .Range(.Cells(1, "D"), .Cells(4, "F"))
Case 3: Set Rng = .Range(.Cells(1, "G"), .Cells(4, "I"))
Case 4: Set Rng = .Range(.Cells(1, "J"), .Cells(4, "L"))
End Select
Rng.CopyPicture Appearance:=xlScreen, Format:=xlPicture
End With
'paste range to table cell (1,1)
.tables(Cnt).Cell(1, 1).Range.Paste
'fit table to range
.tables(Cnt).Columns.AutoFit
End With
Next Cnt
'add text string after table
Txtstr = "Test finished at: " & Now()
wrdDoc.Content.Insertafter Txtstr
'close and save doc
wrdDoc.Close savechanges:=True
Set wrdDoc = Nothing
ObjWd.Quit
Set ObjWd = Nothing
MsgBox "Finished"
Exit Sub

ErFix:
On Error GoTo 0
MsgBox "File error fix"
Set wrdDoc = Nothing
ObjWd.Quit
Set ObjWd = Nothing
End SubI've modified the code slightly so that you only get the paragraph containing your dummy text between the tables, instead of both that and a preceding empty paragraph.

Note: As a matter of policy, I think it's unwise to name a variable 'oWDBasic' as it implies some connection with WordBasic.

[fumei]

macropod: "Au contraire, if you run your code on a one-row two-celled table, you'll get the same result, even though you're only testing the first cell!"

I am not sure to what the au contraire is in reference. I was mentioning your comment that a ("empty") single cell table has three characters, In one way, it has NO characters.


Actually, I was in error for a completely different reason - the end-of-row marker consists of two characters also.

I originally wrote, and you quoted: "The end-of-cell (and end-of-row) marker is, AFAIK, the only instance of a multiple ASCII character. "

Actually, I was not saying you were in error. I was trying to comment that the word "character" in this special case - end-of-cell/end-of-row markers - is a little fuzzy.

But...whatever.

[Dave]

Thanks macropod. Your methods to clear the doc and define the range are new learning for me. I've been unable adapt your code to add a blank line either before the table (after the tabletest text) or after the table using your approach. I would like to control this formatting if you can suggest how. Thanks fumei for your input as well. Dave
ps. the owdbasic thing was repitition of parts of "borrowed" code. I don't know what wordbasic is? I assume it varies from ms Word?

[fumei]

WordBasic was the old version of the language used to code in older versions of Word.

It is NOT the same as VBA, although there is overlap and a fair amount of backward compatibility. However, it am not sure about 2007. There are so many changes in 2007 that I do not know how well it handles WordBasic instructions. I suspect not well. Even 2002/2003 will fail on many WordBasic syntax.

I agree with macropod, using anything that even hints as WordBasic by name is not a good thing.

[geekgirlau]

Those of us who experienced WordBasic first hand still carry the scars :old:

[macropod]

Hi ,

One way of adding another line before/after the 'TableTest' line is to modify the line:
.Content.Insertafter "***TableTest***" & vbCr
For example:
.Content.InsertAfter Chr(11) & "***TableTest***" & Chr(11)
will insert a manual line break before & after the 'TableTest' line, instead of inserting a paragraph break after it.

[Dave]

Thanks to you all for the information and help. Adding Chr(11) as outlined by macropod adds blank lines where I want them. I'll mark this solved. Dave