VBA/Opening multiple word docs in subfolders/Loop

Sworks

New Member
Joined
May 12, 2021
Messages
6
Platform
  1. Windows
Hi,

I'm looking for help on this current code:

Currently code detects all word documents in a specified subfolder of a folder. I would like a loop that does this exact code for the same subfolders in other folders. The subfolder always has the same name but the folder has different names. Can someone help out here?

(code was originally set up for opening pdf's so ignore the conversion bit of the code)


VBA Code:
Sub pdf_To_Excel_Word()
'Macro opens PDF Files as a editable Word Documenta
'Copies the contents of the Word documents
'Pastes the Clipboard contents into Excel


'Declare Variables
Dim myWorksheet As Worksheet
Dim wordApp As Object
Dim myWshShell As Object
Dim strPath As String
Dim oDoc As Object
Dim strFile As String
Dim registryKey As String
Dim wordVersion As String


    'Set Variables
    On Error Resume Next
    Set wordApp = GetObject(, "Word.Application")
    If Err Then
        Set wordApp = CreateObject("Word.Application")
    End If
    On Error GoTo 0


    Set myWshShell = CreateObject("WScript.Shell")
    strPath = "ENTER USER PATH"
    wordVersion = wordApp.Version
    registryKey = "HKCU\SOFTWARE\Microsoft\Office\" & wordVersion & "\Word\Options\"


    'Open and Copy PDF Files
    myWshShell.RegWrite registryKey & "DisableConvertPdfWarning", 1, "REG_DWORD"


    strFile = Dir$(strPath & "*.docx")
    While strFile <> ""
        Set oDoc = wordApp.Documents.Open(fileName:=strPath & strFile, _
                                          ConfirmConversions:=False)
        oDoc.Content.Copy
        'Excel
        Set myWorksheet = ActiveWorkbook.Worksheets.Add
        With myWorksheet
            .Range("A1").Select
            .PasteSpecial Format:="Text"
        End With
        oDoc.Close SaveChanges:=0
        strFile = Dir$()
    Wend


    'Close Word
    wordApp.Quit SaveChanges:=0
    myWshShell.RegWrite registryKey & "DisableConvertPdfWarning", 0, "REG_DWORD"
    'Clear Word and PDF
    Set wordApp = Nothing
    Set myWshShell = Nothing
End Sub
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Forum statistics

Threads
1,214,523
Messages
6,120,039
Members
448,940
Latest member
mdusw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top