none
Use Excel to loop through Word docs; copy/paste contents to multiple cells RRS feed

  • Question

  • How can I loop through word docs in a folder, and copy entire contents of one doc into a cell then, loop to the next word doc and copy/paste that into the next cell?  I Googled around a bit, and tried a few things, but I can't seem to really get started with this task.  Appreciate any/all help.

    Thanks!!


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Friday, August 22, 2014 2:43 AM

All replies

  • This works!!

    Sub GetWordDocContents()
      Dim oWord As Object
      Dim vFiles
      Dim iFile As Integer
      Dim r As Range
      Dim LastRow As Long
       
      vFiles = Application.GetOpenFilename("Word files (*.doc*),*.doc*", Title:="Please select the files you want to copy from", MultiSelect:=True)
      If TypeName(vFiles) = "Boolean" Then Exit Sub ' Cancelled
      Set oWord = CreateObject("Word.Application")
      oWord.Visible = True
      Set r = ActiveSheet.Range("A1")
      For iFile = LBound(vFiles) To UBound(vFiles)
        oWord.Documents.Open vFiles(iFile)
        oWord.ActiveDocument.Select
        oWord.Selection.Copy
        ActiveSheet.Paste r
       
        r.Offset(0, 6).Value = oWord.ActiveDocument.Name
        
        Set r = Cells(ActiveSheet.UsedRange.Rows.Count + 1, 1)
        oWord.ActiveDocument.Close False
    
      Next
      oWord.Quit
      Set oWord = Nothing
      ActiveSheet.Columns.AutoFit
    
        ' Delete row if blank
        Columns("A:A").Select
        For LastRow = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
           If Cells(LastRow, 1) = "" Then Rows(LastRow).Delete
        Next LastRow
        
    End Sub
    


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Saturday, August 23, 2014 10:30 PM