Page numbers or Header Info for embedded Excel file in Word Doc?

Question

I'm trying to search an MS Word doc for embedded Excel files and save them to a different location.

1) I want to record the page number and or section name (based on header style) the embedded file was located in the Word Doc. How can I extract this info?

2) Is there anyway to get the original filename of the embedded Excel file?

Here is the code I'm using to search for embedded files. Originally Working off the code first presented here: Extract Embeded Excel Workseet Data

Sub TestMacro2()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = wdAlertsNone

    Dim lNumShapes As Long
    Dim lShapeCnt As Long
    Dim xlApp As Object
    Dim wrdActDoc As Document
    Dim iRow As Integer
    Dim iCol As Integer

    Set wrdActDoc = ActiveDocument
    For lShapeCnt = 1 To wrdActDoc.InlineShapes.Count
        If wrdActDoc.InlineShapes(lShapeCnt).Type = wdInlineShapeEmbeddedOLEObject Then
            If wrdActDoc.InlineShapes(lShapeCnt).OLEFormat.ProgID = "Excel.Sheet.8" Then
                wrdActDoc.InlineShapes(lShapeCnt).OLEFormat.Edit
                Set xlApp = GetObject(, "Excel.Application")
                cpath = "location of interest"
                xlApp.Workbooks(1).SaveAs cpath & " " & lShapeCnt
                xlApp.Workbooks(1).Close
                xlApp.Quit
                Set xlApp = Nothing
            End If
        End If
    Next lShapeCnt
End Sub

score 0 · Answer 1 · answered Dec 18 '15 at 16:45

Note: Your code would be more efficient (and easier to read) if you assign an object that's re-used to a variable:

Dim ils as Word.InlineShape
Set ils = wrdActDoc.InlineShapes(lShapeCnt)

(1) The Range.Information method can return the page number. Something like:

Dim pageNumber as Long
pageNumber = ils.Range.Information(wdwdActiveEndPageNumber)

The other option is not as straight forward... I expect you really mean Heading style, not Header style. There is a built-in bookmark that will get the Heading preceding the current selection. That would be something like:

Dim secName as String
ils.Range.Select
secName = ActiveDocument.Bookmarks("\HeadingLevel").Range.Text

(2) If the file is not linked then your chances are slim. There's nothing VBA can get at directly, that's certain. Possibly, something might be stored in the WordOpenXML. You can check that by downloading the Open XML SDK Productivity Tool, opening such a document in it and inspecting that part of the Open XML. If it's in there then you can get at it in VBA using ils.Range.WordOpenXML to get the Open XML for the InlineShape, then parse that.

Page numbers or Header Info for embedded Excel file in Word Doc?

1 Answers1