How to edit an EXCEL table object in a WORD document with VB 1

[annettesteinr]

I have a WORD document with an EXCEL table as an object.
I want to add data to this table from a VB program. How to set this object from VB? I found the property: .tables for
word documents, but this does not access the EXCEL table, just normal tables added to the WORD document.

Thanks in advance, Annette

 

[JustinEzequiel]

the Excel Sheet should be (may be) a shape (or inline shape)

(I inserted an Excel sheet into my Word Document via Insert-->Object-->Excel Worksheet)

I tested this and it works (at least here on my Win95 machine with Office 97).
I put this code in the ThisDocument code module.

let me know if this helps (or not)

Option Explicit

Sub test()
  Dim oXLTable As Shape
  
  Dim oWB As Object
  Dim oWS As Object
  
  
  Set oXLTable = ThisDocument.Shapes(1)
  
  With oXLTable.OLEFormat
    Debug.Print TypeName(.ClassType)
    .Activate
    
    Set oWB = .Object
    Set oWS = oWB.Sheets(1)
    
    oWS.Cells(1, 1).Value = "justin"  ' A1
    oWS.Cells(2, 1).Value = 123.005   ' B1
    oWS.Cells(3, 1).Value = Now       ' C1
    
    Set oWS = Nothing
    Set oWB = Nothing
    
  End With
  Set oXLTable = Nothing
End Sub

 

[annettesteinr]

Hi Justin,

Thanks for this quick and good answer. Only VB does not understand OLEFormat. Do I have to activate a special reference? I have activated Word and Excel.

BR, Annette

 

[JustinEzequiel]

sorry, I was testing in Word and not VB

try

[tt]Dim oXLTable As Word.Shape[/tt]