Excel Macro - Save as PDF

bdenn

New Member
Joined
Feb 3, 2017
Messages
29
Office Version
  1. 365
Platform
  1. Windows
Hello,
I have been looking around for a macro that will save an excel sheet as a PDF with the following requirements:
Named the same as the sheet
Add current date
select folder location

I have been messing around with the following code:
Code:
Sub Save_ActSht_as_Pdf()
    ' Saves active sheet as PDF file.


    Dim Name As String
    
    Name = ThisWorkbook.Path & "\" & ActiveSheet.Name & " " & _
        Format(Now(), "mm-dd-yyyy") & ".pdf"
    
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Name, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=False
End Sub

I cant get the the folder location to work in that code, what am I missing?

Thanks,
Bdenn
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
In what way isn't it working?
That code will save the pdf to the same folder that contains the workbook with the code.
 
Upvote 0
If you want to select the folder, try this:

Code:
Sub Save_ActSht_as_Pdf()
  ' Saves active sheet as PDF file.
  Dim Name As String, wFolder As Variant
[COLOR=#0000ff]  With Application.FileDialog(msoFileDialogFolderPicker)[/COLOR]
[COLOR=#0000ff]      .Title = "Select folder"[/COLOR]
[COLOR=#0000ff]      .AllowMultiSelect = False[/COLOR]
[COLOR=#0000ff]      .InitialFileName = ThisWorkbook.Path[/COLOR]
[COLOR=#0000ff]      If .Show <> -1 Then Exit Sub[/COLOR]
[COLOR=#0000ff]      wFolder = .SelectedItems(1)[/COLOR]
[COLOR=#0000ff]  End With[/COLOR]
  '
  Name = wFolder & "\" & ActiveSheet.Name & " " & Format(Now(), "mm-dd-yyyy") & ".pdf"
  ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Name, _
      Quality:=xlQualityStandard, IncludeDocProperties:=True, _
      IgnorePrintAreas:=False, OpenAfterPublish:=False
End Sub
 
Upvote 0
In what way isn't it working?
That code will save the pdf to the same folder that contains the workbook with the code.

Hello,
I’m looking to save the pdf to a different folder location then the original excel document lives.
 
Upvote 0
If you want to select the folder, try this:

Code:
Sub Save_ActSht_as_Pdf()
  ' Saves active sheet as PDF file.
  Dim Name As String, wFolder As Variant
[COLOR=#0000ff]  With Application.FileDialog(msoFileDialogFolderPicker)[/COLOR]
[COLOR=#0000ff]      .Title = "Select folder"[/COLOR]
[COLOR=#0000ff]      .AllowMultiSelect = False[/COLOR]
[COLOR=#0000ff]      .InitialFileName = ThisWorkbook.Path[/COLOR]
[COLOR=#0000ff]      If .Show <> -1 Then Exit Sub[/COLOR]
[COLOR=#0000ff]      wFolder = .SelectedItems(1)[/COLOR]
[COLOR=#0000ff]  End With[/COLOR]
  '
  Name = wFolder & "\" & ActiveSheet.Name & " " & Format(Now(), "mm-dd-yyyy") & ".pdf"
  ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Name, _
      Quality:=xlQualityStandard, IncludeDocProperties:=True, _
      IgnorePrintAreas:=False, OpenAfterPublish:=False
End Sub

Hey,
So what would I need to edit to make it go into this location: C:\Users\Username\Desktop\Holding\

Thanks,
Bdenn
 
Upvote 0
If you always want to use that folder try
Code:
Sub Save_ActSht_as_Pdf()
    ' Saves active sheet as PDF file.


    Dim FName As String
    
    FName = Environ("userprofile") & "\Desktop\Holding\" & ActiveSheet.Name & " " & _
        Format(Now(), "mm-dd-yyyy") & ".pdf"
    
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FName, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=False
End Sub
I would also recommend against using VBA keywords (such as Name) for the names of variables.
 
Last edited:
Upvote 0
If you always want to use that folder try
Code:
Sub Save_ActSht_as_Pdf()
    ' Saves active sheet as PDF file.


    Dim FName As String
    
    FName = Environ("userprofile") & "\Desktop\Holding\" & ActiveSheet.Name & " " & _
        Format(Now(), "mm-dd-yyyy") & ".pdf"
    
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FName, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=False
End Sub
I would also recommend against using VBA keywords (such as Name) for the names of variables.

Thank you all for your help!
It works the way I wanted it too.

-Bdenn
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top