CODEWITHSUNDEEP
vbaexcelpdf
Mantas Volkauskas
Mantas Volkauskas

Reputation: 3

Save Excel Worksheets as Seperate PDFs with a Save Location Dialog Box

I have an Excel file with quite a few worksheets in it (with more to be added eventually). At the moment, I've been going into each sheet and saving them as a PDF individually.

I was wondering if there is a vba macro to save every sheet in the file as a seperate PDF.

I would like the user to have the option of saving the files to a directory of his or her choosing, and I think this would be easiest with a browsing dialog box. The PDFs would be named after the Sheet Name suffixed with " - Some Text".

If this is possible, I would assign the macro to a button in a sort of 'splash' page where I already have a few macro buttons existing.

Also, would there be any way of excluding the 'splash' page from saving as a PDF?

Many Thanks. Mantas

Upvotes: 0

Views: 1062

Answers (1)

quantum285
quantum285

Reputation: 1032

If the splash page is sheet number 1 then this will work:

Sub SaveOutput()
folderChoice = setupFolders()
ChDir (folderChoice)
For i = 2 To Sheets.Count                          'if the splash screen is Sheet 1 it won't be saved.
    Worksheets(i).Select
    Call exportSheet(Sheets(i).Name & " - Some Text")
Next i
End Sub
Function setupFolders()
    MsgBox ("Navigate to output directory...")
    With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        If .Show = 0 Then
            End
        End If
        FolderName = .SelectedItems(1)
    End With
    setupFolders = FolderName
End Function
Sub exportSheet(outputName)
ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=outputName & ".pdf"
End Sub

Upvotes: 1

Related Questions