Adapting VBA from Word to work in Excel

[scapegoat9595]

Hello, I am a total novice when it comes to VBA. I have been using a macro in Word created from pieces all over the internet (credit to a user named matt198992 for recursion script). The code prompts a user for folder, then runs a macro called PublishasPDF to all word files in the folders/subfolders.

I want to adapt the code in Excel, but im having trouble. Error on the line Workbooks.Open Filename:=Path & DirN. All help would be truly appreciated. Thank you.

Sub BatchExceltoPDF()
    
     Dim strFolder As String
 Set fd = Application.FileDialog(msoFileDialogFolderPicker)
 With fd
     .Title = "Select the folder to Convert."
     If .Show = -1 Then
         strFolder = .SelectedItems(1)
         
        Application.Run "personal.xls!Recurrer", (strFolder & "\")
         
     Else
         MsgBox "You did not select a folder"
         strFolder = ""
     End If
 End With

    End Sub

    Sub Recurrer(Path As String)

        Dim DirN        As String
        Dim DirList()   As String
        Dim ndx         As Long
        Dim pos         As Long ' added
       
        ' Add vbSystem, vbHidden, etc., if you want such files
        DirN = Dir(Path, vbDirectory)
       
        Do While DirN <> ""
            If DirN = "." Or DirN = ".." Then
                ' Ignore
            Else
                If (GetAttr(Path & DirN) And vbDirectory) = vbDirectory Then
                    If (Not DirList) = True Then
                        ReDim DirList(0 To 0)
                    Else
                        ReDim Preserve DirList(0 To UBound(DirList) + 1)
                    End If
                    DirList(UBound(DirList)) = DirN
                Else
                    ' DirN has a file name
                    pos = InStrRev(DirN, ".")
                    If pos > 0 Then
                        If InStr("xls xlsx xlsm", LCase(Right$(DirN, Len(DirN) - pos))) Then
                            ' The file is a xls, xlsx or xlsm
                            ' Do whatever with it
                            
                         
    Workbooks.Open Filename:=Path & DirN
                         
    Application.Run "personal.xls!PublishasPDF"
    
    ActiveWorkbook.Close


                         
                         
                        End If
                    End If
                End If
            End If
           
            DirN = Dir ' This just gets the next name before going round again
           
        Loop
       
        ' Now process the saved subdirectories
        If (Not DirList) = True Then
        Else
            For ndx = 0 To UBound(DirList)
                Recurrer Path & DirList(ndx) & Application.PathSeparator
            Next
        End If
       
    End Sub

Sub PublishasPDF()
'
' PublishasPDF Macro
Dim strName As String


With ActiveWorkbook
  strName = .FullName
  strName = Left(strName, InStrRev(strName, ".")) & "pdf"
 ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strName, Quality:=xlQualityStandard, _
        IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:= _
        False
        
    End With
    
   End Sub

 

[scapegoat9595]

Correction: It does work! Just tried with the above file, the one I was working with had an error. All of this time I never thought to try a different folder/file!

 

[scapegoat9595]

Question was also posted on Adapting VBA from Word to work in Excel