VBs and multilevel OLE?

Question

So here is my problem, I have made a vbscript to target some computers and do wmi queries on them, and my boss wants this data to be put inside a document. The problem is that this document is a .doc document with embedded excel objects inside it. Now I have searched wide and far on google on any way to target and manipulate an object inside and object with OLE, but I seem to be getting nowhere.

So my question to you is if someone has some code for this I could look over or maybe a tutorial, and quite possible even tell me if it is even possible?

Answer 1

Some notes based on the chart being an embedded Excel object, as first stated.

''http://msdn.microsoft.com/en-us/library/aa213725(office.11).aspx
''http://msdn.microsoft.com/en-us/library/aa174298(office.11).aspx

    Dim wd ''As Word.Applicatio
    Dim shs ''As InlineShapes
    Dim objChart ''As Excel.Chart
    Dim objSheet ''As Excel.Worksheet
    Dim objOLE ''As Excel.Workbook
    Dim NewSrs ''As Series

    Set wd=CreateObject("Word.Application")
    wd.Documents.Open "C:\Docs\Doc1.docm"
    wd.Visible=True


    Set shs = wd.ActiveDocument.InlineShapes
    ''Just the one shape in this example
    shs(1).OLEFormat.Activate

    ''The OLE Object contained
    Set objOLE = shs(1).OLEFormat.Object

    ''The chart and worksheet
    Set objChart = objOLE.Charts("chart1")
    Set objSheet = objOLE.Worksheets("sheet1")

    objSheet.Range("e1") = "NewData"
    objSheet.Range("e2") = 11
    objSheet.Range("e3") = 12

    Set NewSrs = objChart.SeriesCollection.NewSeries

    With NewSrs
        .Name = "=Sheet1!e1"
        .Values = "=Sheet1!e2:e3"
    End With

Notes for MS Graph

''VBA: Reference: Microsoft Graph x.x Object Library
''Graph Object Model: http://msdn.microsoft.com/en-us/library/aa198537(office.10).aspx

Dim shs ''As InlineShapes
Dim objDS ''As Graph.DataSheet
Dim objOLE ''As Graph.Chart

    Set shs = ActiveDocument.InlineShapes

    ''The OLE Object contained
    shs(3).OLEFormat.Activate
    Set objOLE = shs(3).OLEFormat.Object
    Set objDS = objOLE.Application.DataSheet

    ''00=Corners, Row titles = 01,02 ...
    ''Column titles = A0, B0 ...
    ''Cells = A1, B1 ... E9 ...
    objDS.Range("E0") = "New"
    objDS.Range("E1") = 11
    objDS.Range("E2") = 12
    objDS.Range("E3") = 9

Set objDS = Nothing
Set objOLE = Nothing
Set shs = Nothing

Answer 2

I thought I would share what I came up with in the end, it is a script that checks for the ClassType of the object and uses their respective methods accordingly. It is paired up with an inputbox to let the user select which rows/columns should be checked.

Dim appWord ''Word application object
Dim inshapeSheet ''Inline shapes object
Dim objOLE ''OLE object

Set appWord=CreateObject("Word.Application")
appWord.Application.Documents.Open("C:\doc.doc")
appWord.Visible=True
Set inshapeSheet = appWord.ActiveDocument.InlineShapes

Function count_filled_spaces(intOLENo, strRange)
    ''Activates the the inline shape by number(intOLENo) and defines it as the OLE object
    inshapeSheet(intOLENo).OLEFormat.Activate
    Set objOLE = inshapeSheet(intOLENo).OLEFormat.Object

    ''Detects the ClassType of the inline shape and uses a class specific counter to count which datafields have data
    strClass = inshapeSheet(intOLENo).OLEFormat.ClassType
    i = 0
    If Left(strClass, 8) = "MSGraph." then
        For Each p In objOLE.Application.DataSheet.Range(strRange)
            If p <> "" Then
                i = i+1
            End If
        Next
    ElseIf Left(strClass, 6) = "Excel." then
        For Each p In objOLE.Worksheets(1).Range(strRange)
            If p <> "" Then
                i = i+1
            End If
        Next
    End if
    count_filled_spaces = i
End Function

strRange = InputBox("Lol", "do eeet", "A1:A10")
wscript.echo count_filled_spaces(1, strRange)
wscript.echo count_filled_spaces(2, strRange)
wscript.echo count_filled_spaces(3, strRange)

appWord.Application.Documents.Save
appWord.Application.Documents.Close
appWord.Application.Quit
WScript.Quit(0)