Adapting VBA from Word to work in Excel

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 Publish as PDF 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.

Also posted on http://www.mrexcel.com/forum/excel-q...ml#post3697882

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

You are trying to open a file named "Path & DirN" instead of using the variables Path and DirN to build a file name. Maybe try this change to that line of code:

                            
   Workbooks.Open Filename:=Path & "\" & DirN

See comment below:

Hi, scapegoat9595,

Your post (#6) does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

http://www.mrexcel.com/forum/excel-q...ork-excel.html

Ciao,
Holger