Importing Word Document Properties from Excel File with UDFs

[rhivert]

Hi all,

I would like to verify / identify the formatting and properties for objects, text, paragraphs, etc. within a Word document from an Excel file (using UDFs).

The example below shows a function (UDF) I have written to identify the font name for a specific cell in an Excel document:

Function FontName(MyRef)
Application.Volatile 'Forces recalculation
FontName = MyRef.Font.Name
End Function

Thank you for your help.

R

 

[Yongle]

UDFs below assumes that the document is already open

This should get you started

1 Add a reference to the Word Object library (in VBA under Tools \ References \ then scroll down .. etc)

2 Example PDFs
input required for this is the name of the shape

Function GetShapeFontName(shp As String) As String
    On Error Resume Next
    GetShapeFontName = ActiveDocument.Shapes(shp).TextFrame.TextRange.Font.Name
End Function

input required for this is the paragrapgh number

Function GetParagraphFontName(p As Integer) As String
    On Error Resume Next
    GetParagraphFontName = ActiveDocument.Paragraphs(p).Range.Font.Name
End Function

3 The worksheet

Book1
	A	B	C
1	Details	Formula in column A
2	Lucida Sans	=GetShapeFontName("Text Box 2")
3	Arial Black	=GetParagraphFontName(1)
4
Sheet1

 

[Yongle]

This may be a helpful starting point to get a handle on what the Word document contains
- amend to detail whatever interests you

Sub LoopWord()
'reference required to Word Object library
    On Error Resume Next
    Dim shp As Word.Shape, aList As String
    Const V = vbCr

    With ActiveDocument

        aList = .Name & V
        aList = aList & V & "Table count: " & .Tables.Count
        aList = aList & V & V & "Paragraph count: " & .Paragraphs.Count
        aList = aList & V & V & "Shapes:"

        For Each shp In .Shapes
            aList = aList & V & shp.Name
        Next shp

    End With
   
'print results to immediate window
Debug.Print aList

End Sub

Look for the output in VBA immediate window (make visible when in VBA with {CTRL} G )
- it will look something like this

TheNameOfMyFile.docx

Table count: 5

Paragraph count: 59

Shapes:
Text Box 2
Rectangle 1
Text Box 2
Text Box 2

Shape names repeat - so you may need to use shp.ID instead of shp.Name to reference shapes

 

[rhivert]

Thanks so much Yongle this will help me get started