# word vba, table.column.width returns 999999



## jackni (Sep 22, 2018)

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 (Sep 22, 2018)

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 (Sep 24, 2018)

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 (Sep 24, 2018)

Yongle said:


> 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 (Sep 26, 2018)

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 (Sep 26, 2018)

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 (Sep 26, 2018)

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 (Sep 26, 2018)

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 (Sep 26, 2018)

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 (Sep 27, 2018)

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!!!


----------



## jackni (Sep 22, 2018)

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!!!


----------



## Macropod (Sep 27, 2018)

Since you know which table it is, there is no need to bookmark it, and even that is insufficient on its own: for a table than doesn't span a page break, all you need normally do is to get the vertical position of the last character before, then deduct the: font size & 'space after' setting, then deduct the vertical position of the first character after the table and deduct its 'space before' setting.

If fail to see how the table height would be used to calculate column widths.


----------



## jackni (Sep 27, 2018)

Hi,Paul Edstein
Thank you for reply again! Really appreciated!!!
As you told, when I pasted table to WORD, I calculate the last charecter(wdVerticalPositionRelativeToPage) in table (i.e. T1), then the program changes table width, I read the last charecter(wdVerticalPositionRelativeToPage) again(i.e. T2). when T1<T2, I know some cells in table span in more than one line.
But the same problem exist as when I use bookmark, that is T2 is not changed IMMEDIATELY when the program run byitself, when T2 finnally changed, the columnwidth is too much small.
Any advise about this? Really Thanks!!!


----------



## Macropod (Sep 27, 2018)

One would ordinarily format the range in Excel so it has the desired 'appearance' there, so it doesn't have to be changed again in Word...

Depending on how the table is formatted, neither it's first character nor its last character might define the highest or lowest character, respectively. And, even if they do, they do not tell you what the table's height is. Bookmarking the table, which is quite unnecessary, won't help tell you that, either.

Regardless, if all you're after is to minimise or optimise the table's dimensions, all you need is code like:

```
Sub demo()
    Dim WdObj As Object, WdDoc As Object
    Set WdObj = CreateObject("word.application")
    With WdObj
        .Visible = True
        Set WdDoc = .Documents.Add
        ThisWorkbook.Sheets(1).UsedRange.Copy
        With WdDoc
            .PasteExcelTable False, False, False
            With .Tables(1)
              .PreferredWidthType = wdPreferredWidthAuto
              .Columns.PreferredWidthType = wdPreferredWidthAuto
              .AutoFitBehavior wdAutoFitContent
            End With
        End With
    End With
End Sub
```
No messing around with bookmarks or character positions in sight!


----------



## jackni (Oct 8, 2018)

Paul Edstein,
Sorry for cannot get on to internet these days.
I think I'll use the code you pointed out and try it in my program.
Really appreciated for your patience and reply!!!
Thank you very much!!!


----------

