word vba, table.column.width returns 999999

[jackni]

Hi, All

In my word VBA, the following code returns 999999, what does the result 999999 means?
It's not points, not centimeters, not percent, not any of the length unit used in WORD.

Code:
activedocument.tables(1).columns.width

I'm looking for a code to read the table's width in centimeters, is someone here can help me, Thank you very much!!!

 

[Yongle]

Try this for the first table in the document

Sub WordTableWidth()
    Dim r As Integer, points As Double, cms As Double, tbl As Table
    Set tbl = ActiveDocument.Tables(1)
    
    For r = 1 To tbl.Columns.Count
        points = points + tbl.Columns(r).Width              'measure each column in points and add together
    Next r
    cms = points / Application.CentimetersToPoints(1)       'convert points to cm
    
    MsgBox Round(points, 2) & " points" & vbCr & Round(cms, 2) & " cms", , "Table Width"
End Sub

I think 999999 means that VBA does ot understand what is being asked of it!

 

[jackni]

Yongle,

Now I learned that if you want know a table's width, you have to konw it column by column. Thank you for your reply, the code works fine.
Appreciated very much!

 

[Macropod]

I think 999999 means that VBA does ot understand what is being asked of it!

No, it only means the value hasn't been defined. In the case of Activedocument.Tables(1).Columns.Width, all that means is that cells in the first column don't all have the same width and/or that the table's columns aren't all the same width. Your own code will fail if there are varying-= cell widths in a column. And, since it's the table width that's required, that's ordinarily obtained via code like Activedocument.Tables(1).PreferredWidth, plus you need to know what type of preferred width is being used (i.e. auto, points or %). Hence:

Sub Demo()
Application.ScreenUpdating = False
Dim sngWdth As Single, Rng As Range
Set Rng = Selection.Range
With ActiveDocument.Tables(1)
  .Columns.DistributeWidth
  If .PreferredWidthType = wdPreferredWidthAuto Then
    With .Range.Sections(1).PageSetup
      sngWdth = .PageWidth - .LeftMargin - .RightMargin - .Gutter
    End With
  ElseIf .PreferredWidthType = wdPreferredWidthPercent Then
    With .Range.Sections(1).PageSetup
      sngWdth = .PageWidth - .LeftMargin - .RightMargin - .Gutter
    End With
    sngWdth = (sngWdth * .PreferredWidth) / 100
  Else
    sngWdth = .PreferredWidth
  End If
  Undo
End With
Rng.Select
Application.ScreenUpdating = True
MsgBox PointsToCentimeters(sngWdth)
End Sub

 

[jackni]

Hi, Paul Edstein
Thank you for pointed it out!
When I go on to check the table's height in the way, the insteresting thing is there is no perferredheight or perferredheighttype, could you please tell why.
I want to know how to get the table's height too, and the code is:

for i =1 to activedocument.tables(1).rows.count
    points=points+activedocument.tables(1).rows(i).height
next
msgbox "Table height(cm):" & pointstocentimeters(points)

As you pointed out, the table may also has varying height in a row, does the code can survive in this situation?
Thank you again, Paul Edstein.

 

[Macropod]

Table row heights can be set to automatic, 'at least', or 'exactly'. You test that via:
the .HeightRule property, which has the WdRowHeightRule constants:
• wdRowHeightAuto;
• wdRowHeightAtLeast; and
• wdRowHeightExactly.
The latter two allow one to specify a height in units like points, inches, mm or cm, but only with the last can you be sure the actual height is the same as what is returned by the .Height property; if the .HeightRule property is set to wdRowHeightAtLeast, all you know for sure is that the row height is at least whatever the .Height property returns. For wdRowHeightAuto, the .Height property returns 999999.

You also can't use .Rows.Count where cells have been merged so they span multiple rows (which is what splitting one does to the row it splits).

 

[jackni]

Thank you, Paul Edstein,
Your answer is accurate and professional!
The tables in my WORD are all copied from Excel, so there are varying in different shapes, there maybe exist mergecells.
Because the constents in rows are different, table's row may occupy one row, or several rows.
In this situation, how can I know Table's height?
Thanks!

 

[Macropod]

Unless a table pasted from Excel has merged cells, none of those cells will span multiple rows or columns; they may have multiple lines of content, though. If it does have merged cells and that merge spans multiple rows, you can't loop through the table by row. And, by default, a table pasted from Excel has its row .HeightRule property set to wdRowHeightAtLeast. And, as I already said, all that allows VBA to tell you from the .Height property is each row's minimum height. You have already been told here https://www.mrexcel.com/forum/gener...pies-rows-number-post5148392.html#post5148392 how you might go about calculating such a table's height.

 

[jackni]

Hi, Paul Edstein
Thank you again, really appreciated!
I used what Yongle told me, to add a bookmark at the foot of the table, in order to get the table's height.
And here comes the problem, which is bookmark location cannot updated IMMEDIATELY when columnwidth changed.

Here is the background information：
I'm trying to copy a lot of tables from EXCEL to WORD using vba/vb, in order to make the tables look nice in WORD, I have to adjust column width of each table.
As you know, tables varying in lots of shapes, which means there may exist mergecells or some cells may occupy more than one line/row.
For example,
one 3 * 5 table, in cell(3,2), the contents is much longer than any cells in columns(2), the columns(2)'s perfect width is 5 cm, that means cell(3,2) occupies 2 lines,and other cells in columns(2) occpies one line, which can make the table looks nice. When pasted to WORD, columns(2)'s width is 6 cm, so I use do while... loop to reduce columns(2)'s width by 0.1cm each time, and at the same time, read the bookmark's location(wdVerticalPositionRelativeToPage), if bookmark's vertical location doesnot change,means columns(2)'s width can be reduced, if changed, that means each cell in columns(2) occupy more than one line,and I can know that the table looks not so good. When I run the loop by manual(F8), the program goes well, cause the table and the bookmark can change at once,but when the program run by itself (F5), the table's width and bookmark's location cannot change AT ONCE, so columns(2)'s width is reduced to a very much small value,maybe 1cm. how to solve this problem?
See some part of the code(code saved in Excel vbe) below:

Sub demo()
    Dim bm_old As Single
    Dim myrange As Word.Range
    
    Set wordobj = CreateObject("word.application")
    wordobj.Visible = 1
    wordobj.documents.Add
    
    Set doc = wordobj.ActiveDocument
    Set myrange = doc.Range(doc.Content.End - 1, doc.Content.End - 1)
    ThisWorkbook.Sheets(1).UsedRange.Copy
    myrange.PasteExcelTable False, False, False
    
    With wordobj.Selection.Tables(1)          'add bookmark at the foot of the table
        myrange.SetRange Start:=.Range.End - 1, End:=.Range.End - 1
        doc.bookmarks.Add Range:=myrange, Name:="temp"
    End With
    bm_old = WorksheetFunction.Round(doc.bookmarks("temp").Range.Information(wdVerticalPositionRelativeToPage) / CentimetersToPoints(1), 2)
    *******
    Call columnwidth_adjust(bm_old, 2)
    *******
End Sub


Sub columnwidth_adjust(bm_old As Single, i As Integer)
    Dim bm_new As Single
    Dim myrange As Word.Range
    
    bm_new = bm_old
    wordobj.Selection.Tables(1).Cell(1, 2).Select   'cell(1,2).for example
    With wordobj.Selection
        .Tables(1).AutoFitBehavior (wdAutoFitFixed)
        .SelectColumn
        Do While bm_old = bm_new
            bm_new = 0
            .Columns.PreferredWidth = CentimetersToPoints(thisworkbook.Sheets(2).Cells(1, i) - 0.1) 'this is the place where the columnwidth(in cm) saved
            tiquwb.Sheets("Check").Cells(1, i) = thisworkbook.Sheets(2).Cells(1, i) - 0.1
            bm_new = WorksheetFunction.Round(doc.bookmarks("temp").Range.Information(wdVerticalPositionRelativeToPage) / CentimetersToPoints(1), 2) 'bm_new cannot updated immediately!!!
        Loop
        wordobj.Selection.Tables(1).Cell(i_cellrow, i).Select
        With wordobj.Selection
            .Columns.PreferredWidth = CentimetersToPoints(thisworkbook.Sheets(2).Cells(1, i) + 0.2)
        End With
    End With
    
End Sub

Paul Edstein, how to solve bookmark's location cannot updated immediately? Thank you!!!

 

[jackni]

Hi, Paul or Yongle, or someone can see this

The table height is used to help to calculate the suitable or acceptable columnwidth.
I'm not sure I've made my point clear, if not, please let me know.
And thank you very much! Thanks for your time and advise!!!