Macro to print to PDF saves files to desktop

[rachel06]

Hi, me again!

Hoping this is an easy one. I'm working a macro that's going to open a handful of Excels from a folder and do a few different tasks. One task it's going to do is print the Excel to a PDF.

I've got that part working, kind of - Instead of the PDFs saving to the folder on my desktop that the Excel files are located in, they're going onto my desktop directly. How can I correct this? I'd like them to save to the save file the Excel files are in.

The part I'm having issues with is noted with 'Currently Saves PDFs to Desktop - FIX in the below code but I'm sure you all knew that

Sub ListAllFiles()

Dim MyPath As String
Dim MyFile As String
Dim wb As Workbook
Dim FldrPicker As FileDialog
Dim sh As Worksheet
Dim i As Integer

Set sh = ThisWorkbook.Sheets("Output")
Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

    With FldrPicker
        .Title = "Please Select Folder"
        .AllowMultiSelect = False
        .ButtonName = "Select"
        If .Show = -1 Then
            MyPath = .SelectedItems(1) & "\"
        Else
            End
       End If
    End With
       
MyFile = Dir(MyPath)
i = 2

Do While MyFile <> ""

  
    sh.Cells(i, 1) = MyFile
    MyFile = Dir
    i = i + 1
    
'''''code for open and search here'''''
    
    'Open File
    Set wb = Workbooks.Open(fileName:=MyPath & MyFile)
    
    'Ensure Workbook has opened before moving on to next line of code
     DoEvents
     
    'Save and Close Workbook as PDF
    'Currently Saves PDFs to Desktop - FIX
      ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, fileName:=sPath, _
       Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=True, OpenAfterPublish:=False
      wb.Close SaveChanges:=False
      
'''''code for open and search here'''''
    
Loop




End Sub

Bonus question: after it finishes the last Excel, I get a Run-Time 1004 error that it couldn't find the folder the Excels were in. It says something about "Is it possible it was moved, renamed or deleted?" I'm guessing it has something to do with reaching the end of the Excel files but I'm not sure. I'm kind of just piecing the things I find together to make this work!

Thank you, thank you!!

 

[jolivanes]

What was the result of running the macro in Post #5? (If you have tried it)