CODEWITHSUNDEEP
vbams-word
plaene
plaene

Reputation: 61

Get doc files from folder and subfolders using Word VBA

I am inserting a bunch of Word documents into one file for post-processing. When all the files are in one folder, I got my script to work. However to make it robust for future work, I'd like to insert Word files from all folders and subfolders (and possible futher subs) from a certain starting point. I followed this Youtube tutorial: https://www.youtube.com/watch?v=zHJPliWS9FQ to consider all folders and subfolders and of course amended it for my particular use. 

  Sub CombineDocs()
    On Error Resume Next
    MsgBox "Opening"
    On Error GoTo 0

    Dim foldername As String 'parent folder
    With Application.FileDialog(msoFileDialogFolderPicker)
      .AllowMultiSelect = False
      .Show
      On Error Resume Next
      foldername = .SelectedItems(1)
      Err.Clear
      On Error GoTo 0
    End With

    Documents.Add
    Selection.Style = ActiveDocument.Styles("Heading 1")
    Selection.TypeText Text:="Opening text"
    Selection.TypeParagraph
    Selection.InsertNewPage
    Selection.InsertBreak Type:=wdSectionBreakNextPage
    ActiveDocument.GoTo(What:=wdGoToPage, Count:=2).Select

    Dim fso As Scripting.FileSystemObject
    Dim file As Scripting.file
    getfolders foldername
  End sub

Sub getfolders(foldername)
    Set fso = New Scripting.FileSystemObject
    Call pastedoc(foldername)
    Set fso = Nothing
End Sub

Sub pastedoc(StartFolderPath as String)
    Dim file As Scripting.file
    Dim subfol As Scripting.folder
    Dim mainfolder As Scripting.folder
    Set mainfolder = fso.GetFolder(StartFolderPath )

    For Each file In mainfolder.Files
    If ((InStr(1, LCase(fso.GetExtensionName(file.Path)), "doc", vbTextCompare) > 0) Or _
         (InStr(1, LCase(fso.GetExtensionName(file.Path)), "docx", vbTextCompare) > 0)) And _
                (InStr(1, file.Name, "~$") = 0) Then
        Selection.InsertFile FileName:= _
        file.Path _
        , Range:="", ConfirmConversions:=False, Link:=False, Attachment:=False
        Selection.InsertBreak Type:=wdSectionBreakNextPage
        End If
    Next file

    For Each subfol In mainfolder.SubFolders
        pastedoc subfol.Path
    Next subfol
End Sub

A difference between my code and the tutorial's is that I define the parent folder in the main code and the tutorial does it in the sub script. As a result I get an

'object required'

error in the 'set mainfolder' line. I tried defining all objects and names between the main code and calling the subs but I still can't get it to work. Any guidance what could fix the code?

Upvotes: 1

Views: 1257

Answers (1)

cxw
cxw

Reputation: 17041

One option: assuming the End Sub for CombineDocs was after the getfolders call, you can:

  1. Remove getfolders entirely

  2. In CombineDocs, say pastedoc foldername instead of getfolders foldername

  3. Change the beginning of pastedoc to:

    Sub pastedoc(StartFolderPath as String)
    Dim fso As Scripting.FileSystemObject       ' ** Added
    Set fso = New Scripting.FileSystemObject    ' ** Added

    Dim file As Scripting.file
    Dim subfol As Scripting.folder
    Dim mainfolder As Scripting.folder
    Set mainfolder = fso.GetFolder(StartFolderPath )

    ' ... (everything else the same)

In general, you need to Dim variables either in the Sub where they are used, or at the top of your module, outside any subs. Please put the Dims inside the Subs whenever you can, since that makes your code much easier to change and maintain.

Upvotes: 1

Related Questions