Microsoft Word Table: use Microsoft Excel Cell formatting

Excel screenshot

How can I make cells in Microsoft Word look like the above.

This is what I have so far in the VBA in the Microsoft Word Document which is not bringing over the format from Excel

Set oExcel = New Excel.Application
Set oWB = oExcel.Workbooks.Open(ActiveDocument.Path & "\RevExpenditureTable.xlsx")

Application.ScreenUpdating = True

For Each t In xdoc.SelectNodes("//table")
    For Each r In t.SelectNodes("row")
        For Each c In r.SelectNodes("cell")
            If c.getAttribute("param") <> "" And c.getAttribute("package") <> "" Then
                oExcel.Visible = True
                oWB.ActiveSheet.Cells(1, 1).Value = 10000
                ActiveDocument.Tables(1).Cell(CInt(r.getAttribute("row")), CInt(c.getAttribute("position"))).Range.Text = oWB.ActiveSheet.Cells(1, 1).Value
               
                Application.ScreenRefresh
               
            End If
        Next
    Next
Next
maxdbaseAsked:
Who is Participating?
 
NorieVBA ExpertCommented:
The Text property should return the value from the cell exactly as it's displayed.

Do you have any settings in Word that might eliminate spaces?
0
 
NorieVBA ExpertCommented:
Try changing Value to Text.
                ActiveDocument.Tables(1).Cell(CInt(r.getAttribute("row")), CInt(c.getAttribute("position"))).Range.Text = oWB.ActiveSheet.Cells(1, 1).Text

Open in new window

0
 
maxdbaseAuthor Commented:
Screenshot

That brought it a lot closer.  The $ sign is not positioned to the left though.
0
 
maxdbaseAuthor Commented:
I have not found a setting. However, I tried another computer and got something that looks good.
Not sure if I will get it to work, but will award points as this got me closer :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.