Extract Word Documents to Excel

[KhanZarin]

Hello Forum,

1)
Is it possible to have a code that will copy all the text in a word document that is located in a folder
and paste it on cell A1 in a excel sheet?

2)
If the above is possible, what if i have multiple word documents in a folder
and i would like them all pasted in individual sheets in the same Excel spreadsheet.
One sheet for each word document.

Thank you for any assistance that can be provided.

Hello Forrum, I am a new member here and not much familiar with the posting issues. As my problem is somewhat similar to this one I am posting it here. Hope it will be alright!

I have 64 word files and I want to copy the text/content of each word file into one cell of an excel file. So at the end, the final excel file will have the file names in column A and the text of that file into column B and there would be 64 rows having name and description of 64 word files.

I have been looking for a way to do this almost the whole day. It would be great if anyone can help me with this issue.

Thanks in advance!

 

[Macropod]

Re: Excel and Word - VBA to Copy Word document to excel sheet

An excel cell can contain a maximum of 32,767 characters. That's about 8-10 pages of text. Anything more won't fit. Furthermore, as previously discussed, you'd be liable to lose all the document formatting, as well as headers, footers, footnotes, tables, textboxes, pictures, etc.

 

[KhanZarin]

Re: Excel and Word - VBA to Copy Word document to excel sheet

An excel cell can contain a maximum of 32,767 characters. That's about 8-10 pages of text. Anything more won't fit. Furthermore, as previously discussed, you'd be liable to lose all the document formatting, as well as headers, footers, footnotes, tables, textboxes, pictures, etc.

Thanks for replying macropod!

It is fine as the word files contain only text and each file is less than half a page so it should fit easily in each cell i think. What I am struggling with is a VBA code that would make this task happen. I am not much expert in programming so sharing a script like that would be extremely helpful!

 

[Macropod]

Re: Excel and Word - VBA to Copy Word document to excel sheet

Try the following macro. It includes a folder browser, so all you need to do is to select the folder to process.

Sub GetWordDocTxt()
'Note: this code requires a reference to the Word object model
Application.ScreenUpdating = False
Dim wdApp As New Word.Application, wdDoc As Word.Document
Dim strFolder As String, strFile As String
Dim WkSht As Worksheet, r As Long
strFolder = GetFolder
If strFolder = "" Then Exit Sub
Set WkSht = ActiveSheet
r = WkSht.Cells(WkSht.Rows.Count, 1).End(xlUp).Row
strFile = Dir(strFolder & "\*.doc", vbNormal)
wdApp.Visible = False
While strFile <> ""
  r = r + 1
  Set wdDoc = wdApp.Documents.Open(Filename:=strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
  With wdDoc
    With .Range.Find
      .ClearFormatting
      .Forward = True
      .Wrap = wdFindContinue
      .Format = False
      .MatchWildcards = True
      'Delete Page & Section breaks
      .Text = "^12"
      .Replacement.Text = ""
      .Execute Replace:=wdReplaceAll
      'Delete Paragraph & Line breaks
      .Text = "[^13^l]{1,}"
      .Replacement.Text = "¶"
      .Execute Replace:=wdReplaceAll
      .MatchWildcards = False
      'Delete superfluous white space
      .Text = "^w"
      .Replacement.Text = " "
      .Execute Replace:=wdReplaceAll
    End With
    WkSht.Range("A" & r).Value = Split(strFile, ".doc")(0)
    WkSht.Range("B" & r).Value = Left(Left(.Range.Text, Len(.Range.Text) - 1), 32767)
    .Close SaveChanges:=False
  End With
  strFile = Dir()
Wend
wdApp.Quit
'Restore paragraph/line breaks
WkSht.Range("B1:B" & r).Replace What:="¶", Replacement:="" & Chr(10) & "", LookAt:=xlPart, _
  SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
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

 

[KhanZarin]

Re: Excel and Word - VBA to Copy Word document to excel sheet

Hi Edstein,

Thank you so much for the help! As far I understood from your script, once I run the code it will ask for the location of the files? Please correct me if I am wrong!

I did follow it and had the message "user defined type not defined" with the 'wdApp As New Word.Application', in line 4 being highlighted in blue, I guess I am missing something very simple.

 

[Macropod]

Re: Excel and Word - VBA to Copy Word document to excel sheet

I did follow it and had the message "user defined type not defined" with the 'wdApp As New Word.Application', in line 4 being highlighted in blue, I guess I am missing something very simple.

As indicated in the note on the line immediately above the one that generated the error message, the code requires a reference to the Word object model. You set that reference in the VBE via Tools|References and selecting the Microsoft Word object library.

 

[KhanZarin]

Re: Excel and Word - VBA to Copy Word document to excel sheet

Hi again! See that was a really silly thing I missed! I am actually new with MACROS....... I followed your advice and this time it actually ran! :D
However, I am getting only the file names in column A and for a couple of files only the first word came to column B. Most of the cells of column B are blank which means the test description did not import! BTW all the files started with a heading style but it is fine if the style does not remain in the excel cell! Do you think the heading style has to do anything with this?
I am trying alternative way now and thanks a lot for the time but if you know the issue, sharing it will be highly appreciated!

 

[Macropod]

Re: Excel and Word - VBA to Copy Word document to excel sheet

You may need to expand or click in the cells to see the content, especially if the documents started with empty paragraphs. As for the Heading Styles, that's of no consequence; as I said, you lose all formatting (not the content).

 

[KhanZarin]

Re: Excel and Word - VBA to Copy Word document to excel sheet

Argh.......yes you are right! Thanks a lot, you're the best.