Copy word file content & process it in Excel

[Robie]

Hi

I need to take several different types of input from Word documents (one at a time) and copy them Excel, compare it to existing column of items and create a ticked list.

Is it possible to write some VBA (presumably from Excel) to get content from Word, compare it to 1st column data in Excel? So, quite simply, each column E onwards represents different output and whether it supports the particular field in column A. If it supports it then we put a tick there otherwise leave it blank. Each column (from E onwards) represents output from different Word files. Obviously we do one word file at time.

The output is similar to the attached.

58.png

Thanks.

[Rudi]

So what I can see is that it is not transferring data to the Excel report but rather just checking if that component is present in the Word document and marking it if it is.

If this is correct, can you supply more detail to what these components are?
What does Header: BeginString mean for example?

[Robie]

So what I can see is that it is not transferring data to the Excel report but rather just checking if that component is present in the Word document and marking it if it is.

If this is correct, can you supply more detail to what these components are?
What does Header: BeginString mean for example?

Thanks for quick response Rudi.

You say: not transferring data to the Excel report but rather just checking if that component is present in the Word document and marking it if it is. I didn't think of that but essentially yes.
You say: What does Header: BeginString mean for example? Basically, treat columns A & B as text only. We compare to the content of the Word document and mark if it matches column A & B.

Hope this makes sense.

[Rudi]

>> Basically, treat columns A & B as text only. We compare to the content of the Word document and mark if it matches column A & B.

I'm still confused?
Must the code search for the text "BeginString" in the document header and if present, tick it in Excel?

[Robie]

>> Basically, treat columns A & B as text only. We compare to the content of the Word document and mark if it matches column A & B.

I'm still confused?
Must the code search for the text "BeginString" in the document header and if present, tick it in Excel?

Ummm - sorry for the confusion.

If it makes it easier - ignore column A.

Therefore, think of it like this:
1. Word document has a list of fields (or a table with a column) with names such as 'BeginString', BodyLength, MsgType, etc.
2. Compare the content from Word for each cell to Excel column B.
3. If it matches then tick it otherwise leave blank
4. Process the next cell (row) from Word and start from step 2.

Hope this is clearer.

[HansV]

Do you want to enter the file name of in a cell, say E2, F2 etc., and then let the macro process that document?
Or do you want to let the macro display a dialog in which the user can select a document?
Or would you like to process an entire folder of Word documents?

[Robie]

Do you want to enter the file name of in a cell, say E2, F2 etc., and then let the macro process that document?
Or do you want to let the macro display a dialog in which the user can select a document?
Or would you like to process an entire folder of Word documents?

Excellent points Hans.

2. Display dialog and let the user select the Word document please.

Thanks.

[HansV]

See if you can adapt this for your purpose:

Code: Select all

Sub ProcessDoc()
    Dim strFile As String
    Dim r As Long
    Dim m As Long
    Dim c As Long
    Dim f As Boolean
    Dim wrd As Object
    Dim doc As Object

    strFile = Application.GetOpenFilename(FileFilter:="Word documents (*.doc*),*.doc*")
    If strFile = "False" Then
        MsgBox "You haven't selected a document!", vbExclamation
        Exit Sub
    End If

    On Error Resume Next
    Set wrd = GetObject(Class:="Word.Application")
    If wrd Is Nothing Then
        Set wrd = CreateObject(Class:="Word.Application")
        f = True
    End If
    On Error GoTo ErrHandler

    Set doc = wrd.Documents.Open(strFile)
    Application.ScreenUpdating = False
    c = Cells(1, Columns.Count).End(xlToLeft).Column + 1
    Cells(1, c).Value = "Document"
    Cells(2, c).Value = doc.Name
    m = Cells(Rows.Count, 2).End(xlUp).Row
    For r = 3 To m
        If Cells(r, 2).Value <> "" Then
            If doc.Content.Find.Execute(FindText:=Cells(r, 2).Value, MatchCase:=False) Then
                Cells(r, c).Value = ChrW(&H2713)
            End If
        End If
    Next r

ExitHandler:
    On Error Resume Next
    doc.Close SaveChanges:=False
    If f Then
        wrd.Quit
    End If
    Application.ScreenUpdating = True
    Exit Sub

ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
End Sub

[Robie]

See if you can adapt this for your purpose:

Code: Select all

Sub ProcessDoc()
    Dim strFile As String
    Dim r As Long
    Dim m As Long
    Dim c As Long
    Dim f As Boolean
    Dim wrd As Object
    Dim doc As Object

    strFile = Application.GetOpenFilename(FileFilter:="Word documents (*.doc*),*.doc*")
    If strFile = "False" Then
        MsgBox "You haven't selected a document!", vbExclamation
        Exit Sub
    End If

    On Error Resume Next
    Set wrd = GetObject(Class:="Word.Application")
    If wrd Is Nothing Then
        Set wrd = CreateObject(Class:="Word.Application")
        f = True
    End If
    On Error GoTo ErrHandler

    Set doc = wrd.Documents.Open(strFile)
    Application.ScreenUpdating = False
    c = Cells(1, Columns.Count).End(xlToLeft).Column + 1
    Cells(1, c).Value = "Document"
    Cells(2, c).Value = doc.Name
    m = Cells(Rows.Count, 2).End(xlUp).Row
    For r = 3 To m
        If Cells(r, 2).Value <> "" Then
            If doc.Content.Find.Execute(FindText:=Cells(r, 2).Value, MatchCase:=False) Then
                Cells(r, c).Value = ChrW(&H2713)
            End If
        End If
    Next r

ExitHandler:
    On Error Resume Next
    doc.Close SaveChanges:=False
    If f Then
        wrd.Quit
    End If
    Application.ScreenUpdating = True
    Exit Sub

ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
End Sub

Wow. Thanks Hans for a quick response.

Can it really be that simple? Thanks. BTW: It will take mew few days to get this sorted so perhaps will reply next week as to how I got on. Thanks once again.

[HansV]

This version looks only at column B of the worksheet. If you want to look at columns A and B, you'd have to explain how we should search the Word document for two text strings.

[Robie]

This version looks only at column B of the worksheet. If you want to look at columns A and B, you'd have to explain how we should search the Word document for two text strings.

Thanks Hans.

Out of interest: Why are you defining wrd as Object rather than Word.Application or is it one and the same thing? Thanks.

[HansV]

The code as written uses late binding, i.e. it does not require you to set a reference to the Microsoft Word n.0 Object Library in Tools > References... in the Visual Basic Editor. The advantage of this approach is that it won't matter if the end user has an older version of Microsoft Office than you have.

The alternative would be to use early binding: set a reference to the Microsoft Word n.0 Object Library, and declare wrd and doc as follows:

Code: Select all

    Dim wrd As Word.Application
    Dim doc As Word.Document

The advantage of this approach is that IntelliSense will work for the Word objects: if you type doc. a list of properties and methods of the Document object will pop up.
But if you create the workbook in Excel 2013, a user with Office 2007 or 2010 would get an error message because the reference to the Microsoft Word 15.0 Object Library isn't recognized. (There are no problems the other way round: later versions recognize references to earlier versions)

[Robie]

The code as written uses late binding, i.e. it does not require you to set a reference to the Microsoft Word n.0 Object Library in Tools > References... in the Visual Basic Editor. The advantage of this approach is that it won't matter if the end user has an older version of Microsoft Office than you have.

The alternative would be to use early binding: set a reference to the Microsoft Word n.0 Object Library, and declare wrd and doc as follows:

Code: Select all

    Dim wrd As Word.Application
    Dim doc As Word.Document

The advantage of this approach is that IntelliSense will work for the Word objects: if you type doc. a list of properties and methods of the Document object will pop up.
But if you create the workbook in Excel 2013, a user with Office 2007 or 2010 would get an error message because the reference to the Microsoft Word 15.0 Object Library isn't recognized. (There are no problems the other way round: later versions recognize references to earlier versions)

Excellent explanation Hans - thank you.

For my needs, I am happy with your original recommendation.
As regards to different versions of Microsoft Office, I am doing this for internal company use only and we all have Office 2010. For now, I don't think I need to worry about using the where we need to reference the Word Object library but at least the option is there if required in the future (e.g. change in requirements).

Thank you once again.

[snb]

This might be sufficient:

Code: Select all

Sub M_snb()
   sn = Sheets(1).Cells(3, 1).CurrentRegion.Resize(, 6)

   With Application.FileDialog(msoFileDialogOpen)
        .Filters.Add "Word documents", "*.doc*", 1
        If .Show Then
             With GetObject(.SelectedItems(1))
                c00 = .Content
                For j = 1 To UBound(sn)
                    If InStr(c00, sn(j, 2)) Then sn(j, 5) = ChrW(&H2713)
                 Next
                .Close 0
              End With
              
              Sheets(1).Cells(3, 1).CurrentRegion.Resize(, 6) = sn
        End If
    End With
End Sub

PS. It's not clear to me when you check in column E and/or in Column F

This code runs fast because:

- the Word file will remain invisible
- alle the results wil be written into the worksheet in 1 go.