VBA Code Needed For Word to Excel

[minatrea]

Hello all,

Excel 2013 & Word 2013.

I have a directory full of .doc files. Let's call it C:\BOM.
I will generate a .txt file of certain files from that directory and save it as C:\BOM\BOM.txt.

Example: BOM.txt

C:\BOM\file01.doc
C:\BOM\file05.doc
C:\BOM\file11.doc
C:\BOM\file47.doc​

I need an Excel VBA code that will do the following:

Open an already created excel file, named C:\BOM\Book1.xlsm.

Open file01.doc, Select All, and Copy.
Select the open excel file C:\BOM\Book1.xlsm.
Create a sheet called "file01".
Select A1 on sheet "file01" and Paste.
Close file01.doc.

Open (the next file in BOM.txt) file05.doc, Select All, and Copy.
Select the already open C:\BOM\Book1.xlsm.
Create a sheet called "file05".
Select A1 on sheet "file05" and Paste.
Close file05.doc.

I would like to continue to do this for all the files listed in BOM.txt, and then:

Save Book1.xlsm.
Close Book1.xlsm.

I don't code, so I don't know if this is the logical way to do this. For example, if it needed to create all the sheets first and then copy and paste, that would be fine. At the end of the day, I just need a workbook, multiple sheets that are named after the files in that list, and populated with the data that was selected and copied.

Thanks in advance for your help and consideration. If someone can help, I may be able to see my family some time this month!

 

[Macropod]

Since a macro requires the file running it to be open, you may as well add that macro to your Book1.xlsm file and run it from there. Doing so obviates the need for a separate file for the macro and, hence, for the macro to open your Book1.xlsm file.

Sub GetDocumentContent()
'Note: this code requires a reference to the Word object model.
'See under the VBE's Tools|References.
Application.ScreenUpdating = False
Dim WkSht As Worksheet, wdApp As New Word.Application, wdDoc As Word.Document
Dim strFolder As String, strFile As String, StrFiles As String
strFolder = ThisWorkbook.Path & "\": StrFiles = "|"
'Disable any auto macros in the documents being processed
wdApp.WordBasic.DisableAutoMacros
wdApp.DisplayAlerts = wdAlertsNone
'Get the file data from the filter file
Open strFolder & "BOM.txt" For Input As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
Do While Not EOF(1)
  Line Input [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , strFile
  'Check the document exists
  If (strFile <> "") And (Dir(strFolder & strFile) <> "") Then
    'Create a worksheet for the document
    Set WkSht = Sheets.Add
    WkSht.Name = Split(strFile, ".doc")(0)
    'Get the document's content
    Set wdDoc = wdApp.Documents.Open(Filename:=strFolder & "" & strFile, AddToRecentFiles:=False, ReadOnly:=True, Visible:=False)
    With wdDoc
      .Range.Copy
      WkSht.Paste Destination:=WkSht.Range("A1")
      .Close SaveChanges:=False
    End With
  End If
Loop
Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
'Clean up and exit
wdApp.DisplayAlerts = wdAlertsAll
wdApp.Quit
Set wdDoc = Nothing: Set wdApp = Nothing: Set WkSht = Nothing
Application.ScreenUpdating = True
End Sub