Multiple sheets with names and data from Word files

[knpaddac]

I have approximately 1,200 Word files and I want to create a single Excel file that has a sheet titled for each of those Word files. ALSO, I want to copy all of the contents of each Word file into A1 of the sheet that matches it. Is this possible to do with a macro? Otherwise, I have alot of copy/paste to do.

 

[Macropod]

Try something based on:

Sub GetWordDocumentData()
'Note: this code requires a reference to the Word object model, added via Tools|References
Application.ScreenUpdating = False
Dim StrFolder As String, StrFile As String, WkSht As Worksheet
StrFolder = GetFolder
If StrFolder = "" Then Exit Sub
Dim wdApp As New Word.Application, wdDoc As Word.Document
wdApp.DisplayAlerts = wdAlertsNone
StrFile = Dir(StrFolder & "\*.doc")
While StrFile <> ""
  On Error Resume Next
  Set WkSht = Sheets.Add
  MsgBox Split(StrFile, ".doc")(0)
  WkSht.Name = Split(StrFile, ".doc")(0)
  Set wdDoc = wdApp.Documents.Open(Filename:=StrFolder & "\" & StrFile, AddToRecentFiles:=False, Visible:=False)
  With wdDoc
    .Range.Copy
    .Close SaveChanges:=False
  End With
  WkSht.PasteSpecial Format:="Microsoft Word 8.0 Document Object"
  StrFile = Dir()
Wend
wdApp.Quit
Set wdDoc = Nothing: Set wdApp = Nothing: Set WkSht = Nothing
Application.ScreenUpdating = True
End Sub

Function GetFolder() As String
Dim oFolder As Object
GetFolder = ""
Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
Set oFolder = Nothing
End Function

Since you haven't said how you want the data to appear in Excel, I've inserted them as Word objects as that best preserves the original layout.

 

[knpaddac]

Wow. Was not expecting anything so quickly. Thanks.
I suppose that I probably could have explained a little better. What I do (if I was doing each manually) is that I highlight and copy all of the text in the Word document and then use the 'match destination formatting' option under the paste command in Excel. This then puts all the data into separate cells, columns and rows, in the Excel file.
It is possible that what you have done already fits this, but I do have to ask, is it possible you could give a little info on what exactly I have to put into the Macro and highlight where I need to put it to work correctly?

 

[Macropod]

You could try changing:
WkSht.PasteSpecial Format:="Microsoft Word 8.0 Document Object"
to:
WkSht.Paste Destination:=WkSht.Range("A1")
or:
WkSht.Range("A1").PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:=False, NoHTMLFormatting:=True

PS: you could also delete:
MsgBox Split(StrFile, ".doc")(0)
That line's just left over from testing.

 

[knpaddac]

Okay. Do I need to put somewhere the name of the folder that contains all of the Word documents that are being used?

 

[Macropod]

Not unless you want to. The macro already includes a folder browser, which allows you to select any folder.

 

[knpaddac]

I have put it in, and made changes as you suggested, but whenever I run it I get an error "Compile error: User-defined type not defined" and the text "wdApp As New Word.Application" is highlighted.

 

[Macropod]

Did you read the first line in the macro? What do you mean by "I have put it in"? As written, the code doesn't require (or provide for) you to put anything in.

 

[knpaddac]

I created a new macro in my Excel document and copied your initial code from above into that macro. Then made the changes you mentioned in comment #4. When I click 'run' I am getting the previously mentioned errors.
Maybe I am doing something wrong? Maybe this is beyond my abilities?

 

[Macropod]

The code I posted was an entire stand-alone macro. All you needed to do was to:
• add it to an appropriate Excel workbook
• add the Word references
• maybe, change the line:
WkSht.PasteSpecial Format:="Microsoft Word 8.0 Document Object"
• and delete the line:
MsgBox Split(StrFile, ".doc")(0)
If you don't do the second step, expect errors.