Moving cell data into Word table

[Shaun]

Hi All

I am trying to get data from excel in to a MS Word document and I have found some code which gets me most of the way, but I am not sure how to modify to take the data from the cells and enter into the table.

There are a few conditions for the Word table:

1. row one will always be the header; and
   
2. the last row will always be the total; and
   
3. the number of rows in the table between the header and total row varies from 1 to 20.

Is there a better way to achieve the desired outcome?

Please find attached the example file.

I am hoping someone will know what changes are required to make it work, and any assistance offered will be greatly appreciated.

Cheers

Shaun

 

[wudixin96]

Function FnAddEstNPBT()
  Dim arrData
  Dim cell As Range
  Dim N As Long
  Dim intNoOfRows
  Dim intNoOfColumns
  Dim objWord
  Dim objDoc
  Dim CountA As String
  Dim CountBlank As String
 
  arrData = Range("B5:G26")
    'intNoOfRows = WorksheetFunction.CountA(Range("B5:B26")) - WorksheetFunction.CountBlank(Range("B5:B26"))
    intNoOfColumns = 6
Debug.Print intNoOfRows, intNoOfColumns

  Set objWord = CreateObject("Word.Application")
  objWord.Visible = True
  Set objDoc = objWord.Documents.Add
  Set objRange = objDoc.Range
  objDoc.Tables.Add objRange, 1, intNoOfColumns
  Set objTable = objDoc.Tables(1)
    objTable.Borders.Enable = False
'
' This is the section that need amending to input the data from the range
'
  For i = 1 To UBound(arrData)
    If arrData(i, 1) <> "" Then
        If i > 1 Then objTable.Rows.Add
        For j = 1 To intNoOfColumns
            objTable.cell(i, j).Range.Text = arrData(i, j)
    '
    '
    '
        Next
    End If
  Next
End Function

Like this ???

 

[Shaun]

Hi wudixin96

Almost exactly like that!

I had hoped that the data went across in the same format as in the spreadsheet but that does not appear to be the case. Is it possible to retain the formating, especially the number formatting.

Is it possible to do that?

Thank you for your assistance, it is greatly appreciated

Cheers

Shaun

 

[Shaun]

Hi All

I am really struggling with the formating of the text that is sent over to Word. Would someone please take a look?

I have attached the updated example file and what I am trying to achieve in a docx file. I changed the file extension to txt to upload.

Cheers

Shaun

 

[NARAYANK991]

Hi Shaun ,

The correct syntax is :

objtable.cell(2, 2).Range.Text = VBA.Format(VAL(objtable.cell(2, 2).Range.Text), "#,##0")

Narayan

 

[Shaun]

Hi Narayan

Thank you, I have spent hours and hours researching how to do that.

Are you aware of an online resource that would allowed me to arrive at that? I have read so many pages but nothing I saw looked anything like that!

The same is also true for right aligning the text in columns 2 to j. I have searched and searched but nothing seems to work

How could that be made to format all the cells in the range 2, 2 to i, j?

I am currently trying to work out how to identify the last cell in the table so that I can the select the range and format all the cells in the range to #,##0.

Cheers

Shaun

 

[NARAYANK991]

Hi Shaun ,

One of the ways is to use the Object Browser , which when you click on a property or a method , shows you to which class it belongs.

objtable is a Table object , which is a member of Word.

When you select Table in the left side of the Object Browser , the right side will display all the properties and methods of the Table object ; Cells is nowhere in the list. Cell is displayed , and in the details , you will see :

Function Cell(Row As Long, Column As Long) As Cell
Member of Word.Table

Secondly , because the Text property has been used to retrieve the data contained in the document table , the format string cannot be applied , since the format string is used to convert a numeric value to text. Hence , if you have to get formatted text from a text string , you need to first convert it to a numeric value , and then apply the desired format string to it.

Narayan

 

[NARAYANK991]

Hi Shaun ,

Use the following :

objtable.Cell(objtable.Rows.Count, objtable.Columns.Count).Range.Text

will refer to the last cell in the table.

Narayan

 

[Shaun]

Hi Narayan

Thank you so much for your guidance.

Following on from your comments, I think I have it sorted, it took me a while, but:

Dim myrows As String
myrows = objtable.Rows.Count
If myrows > 1 Then
  For i = 2 To myrows
    For j = 2 To intNoOfColumns
      objtable.cell(i, j).Range.Text = VBA.Format(Val(objtable.cell(i, j).Range.Text), "#,##0")
      objtable.cell(i, j).Range.ParagraphFormat.Alignment = 2
    Next
  Next
End If

It formats all the cells in the range 2, 2 to i, j to #,##0 and right justify.

Have I arrived at the correct conclusion in thinking that you cannot select ranges of cells in Word tables in the same way you can in Excel? You can only "work" cell by cell? Every effort that I made to work with cell ranges failed.

Cheers

Shaun

 

[NARAYANK991]

Hi Shaun ,

The Rows property allows you to look at an entire row ; if anything that you want done will apply to the entire row , probably you can use this.

I think ( I am not sure ) that in Word , the concept of a range as in Excel does not exist ; a table consists of rows and columns , and you can access them ; you can also access each individual cell of a table.

I think the VBA follows the Word menu structure , where for a table you will find the tabs labelled Table , Row , Column and Cell.

Narayan