Pulling a Date from a Word File Into Excel

[CHeil402]

Hi,
I was just wondering if it possible to pull data from a word document into excel. What I have is a standard template that all letters are written from, and I want to pull the dates of the letters into a spreadsheet. Is there a formula to pull the date from the letters, knowing that the dates are going to be in the same spot in every letter? Thanks.

 

[pbornemeier]

If the date field in the template has a name you should be able iterate through all files (word docs) in a directory and pull that value of that date field. If there is not a name, and the dates is in the EXACT same spot, you would be able to move the workd cursor to the appropriate spot and copy that data, then paste it in Excel.

Here is some code to iterate through all .doc files in a folder you select:

Option Explicit
Sub IterateFiles()
    Dim sFilePath As String
    Dim sFileName As String
    Dim sThisDocument As String
    Dim iX As Integer
    Dim iLastSlash As Integer
    'Iterate through workbooks in directory
 
    'User set a path or get the default path.
    sFilePath = Application.GetOpenFilename("Select a directory, or Cancel to choose this file's directory")
 
    If sFilePath = "" Then sFilePath = ThisWorkbook.Path
    For iX = 1 To Len(sFilePath)
        If Mid(sFilePath, iX, 1) = "\" Then
            iLastSlash = iX
        End If
    Next
    sFilePath = Left(sFilePath, iLastSlash - 1)
 
    sThisDocument = ThisWorkbook.Name
    'Get the first filename on that path
    sFileName = Dir(sFilePath & "\*.doc")      ' Retrieve the first entry.
 
    Do While sFileName <> ""     ' Start the loop.
 
        If sFileName <> sThisDocument Then
            'do stuff
            Debug.Print sFileName
        End If
        sFileName = Dir
    Loop
End Sub

Use the following to figure out how to pull the dates:

Some good word VBA tips here:
http://www.thezcorp.com/VBACodeSamples.aspx

And tips on using word from Excel:
http://www.mrexcel.com/articles/excel-to-word-macro.php