How to refer to excel cells attribute

[Alex St-Pierre]

Hi, I would like to use excel property in Word VBA.
The problem I have is that I format Word Table using Table in Excel and
would like to adjust to table depending of all cells format in excel.
So, I open the Excel workbook and copy a table value to word. I would like
to refer to Excel cells borders and text style (ex: bold) to be able to
adjust word table but doesn't know how ?

Dim appExcel As Object
Dim wbExcel As Object
Dim rngExcel as Range
Dim tbl As Word.Table
Dim DocWord1 As Word.Document
Dim strData as String
Set DocWord1 = AppWord.ActiveDocument
PathExcel = "table.xls"
Set appExcel = GetObject(, "Excel.Application")
Set tbl = DocWord1.Tables(2)
Set wbExcel = appExcel.Workbooks(PathExcel)
Set rngExcel = wbExcel.Sheets("table1.1").Range("Table1_1")
Set tbl = DocWord1.Tables(2)
DerLigneExcel = rngExcel.Rows.Count
DerLigneWord = tbl.Rows.Count
j = DerLigneExcel - DerLigneWord
For k = 1 To j
tbl.Rows.Add
Next k
For k = 1 To -j
tbl.Cell(5, 1).Select
Selection.SelectRow
Selection.Rows.Delete
Next k
With tbl
For i = 1 To tbl.Rows.Count
For j = 1 To tbl.Columns.Count
strData = rngExcel.Cells(i, j)
strData = Format(strData, "#,##0;(#,##0)")
.Cell(i, j).Range.Text = strData
'I would like to say:
' If rngExcel.Cells(i, j).Borders(x1EdgeTop).LineStyle = x1continue Then
' etc.
' End If 'Also, if this work, I will be able to check if the style is
bold.

Next j
Next i

Thanks.

 

[Helmut Weber]

Hi Alex,

use early binding:

http://word.mvps.org/faqs/interdev/EarlyvsLateBinding.htm

See also:
http://word.mvps.org/faqs/interdev/controlxlfromword.htm
http://word.mvps.org/faqs/interdev/controlwordfromxl.htm

Without looking into you code precisely,
it's probably the excel constants,
which are causing trouble,
because they are unknow to Word with late binding.

also, with

Dim rngExcel as Range

rngExcel is a word range.

Should be:

Dim rngExcel as Excel.Range

Plus
(x1EdgeTop).LineStyle = x1continue

should be xlEdgeTop and xlcontinue, I suppose.

Note the differnce: not "x1", but "xl".

So far for a beginning.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Alex St-Pierre]

Hi Helmut,
With Early Binding, all works good. I can write my code as I write it in
Excel VBA.
This morning, since I opened the document from office XP, in
Tools/Reference, I had to set the object library version 10.0. ("Missing:
Microsoft Excel 11.0 Object Library")

The program I am creating will be used by a lot of people from office xp
version to newest. Is there a way to add things in my Macro that will replace:
"Missing: Microsoft Excel 11.0 Object Library" by the last Microsoft Excel
Object Library available ? (and, if available, add a line that remove all
missing status)
Thanks,

 

[Helmut Weber]

Hi Alex,

you may use early binding for development.
Use the numeric values of the Word-constants.
Use late binding when testing is finished.