How to save to specific directory when doing ExportAsFixedFormat?

[romtoss]

Hello!

I have a workbook with 39 tabs that need to be saved individually as PDFs. To save myself time, I created a macro that does this. Code is below. Problem is, I can't get the PDFs to be saved/exported to a specific folder consistently. I've tried a few solutions I looked up online, but I must be doing something wrong. I tried "ChDir" followed by a path, but they always save to My Documents and I don't want them there. Can anyone provide a simple solution here?

Sub Save_PDFs()


    For n = 1 To 39

    ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=ActiveSheet.Name, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=False, _
        IgnorePrintAreas:=False, _
        OpenAFterPublish:=False

        ActiveSheet.Next.Select

    Next n


End Sub

 

[John_w]

Try this, changing the saveInFolder string as required.

Public Sub Save_Each_Sheet_As_PDF()

    Dim saveInFolder As String
    Dim ws As Worksheet
    
    saveInFolder = "C:\path\to\folder\"
    If Right(saveInFolder, 1) <> "\" Then saveInFolder = saveInFolder & "\"
    
    For Each ws In Worksheets
        ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveInFolder & ws.Name & ".pdf", _
            Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False
    Next
    
End Sub

 

[romtoss]

Hmm, unfortunately that didn't work either. They still save to My Documents, regardless of what path I specify.

 

[Domenic]

John's solution should work. What string are you assigning to saveInFolder?

 

[romtoss]

John's solution should work. What string are you assigning to saveInFolder?

I've tried several, both on our network (here at work) and local to my machine. No matter what I do, it saves to My Documents. Currently I have this:

    Dim saveInFolder As String
    saveInFolder = "C:\Users\rostom\Desktop\"

 

[Domenic]

Can you post the complete code that you're using?

 

[romtoss]

Can you post the complete code that you're using?

Here it is.

Sub Save_PDF()


    Dim saveInFolder As String
    saveInFolder = "C:\Users\rostom\Desktop\"


    Worksheets("Template - Rep Detail").Select


    For n = 1 To 39
        ActiveSheet.Next.Select
        Cells.EntireColumn.AutoFit


        ActiveSheet.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=ActiveSheet.Name, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=False, _
            IgnorePrintAreas:=False, _
            OpenAFterPublish:=False
    Next n


End Sub

 

[Domenic]

While you've assigned saveInFolder the path, you haven't included it for your path in the argument for Filename. Also, you may want include the file extension as well. Try...

Filename:=saveInFolder & ActiveSheet.Name & ".pdf", _

 

[romtoss]

While you've assigned saveInFolder the path, you haven't included it for your path in the argument for Filename. Also, you may want include the file extension as well. Try...

Filename:=saveInFolder & ActiveSheet.Name & ".pdf", _

Doh! That worked... silly me. Thank you so much!!

 

[Domenic]

You're very welcome!

Cheers!