Excel Macro - Save as PDF

bdenn

New Member
Joined
Feb 3, 2017
Messages
13
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,488
Office Version
365
Platform
Windows
In what way isn't it working?
That code will save the pdf to the same folder that contains the workbook with the code.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,891
Office Version
2007
Platform
Windows
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
 

bdenn

New Member
Joined
Feb 3, 2017
Messages
13
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.
 

bdenn

New Member
Joined
Feb 3, 2017
Messages
13
Hello,
I will try this out and see thanks.
 

bdenn

New Member
Joined
Feb 3, 2017
Messages
13
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,488
Office Version
365
Platform
Windows
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:

bdenn

New Member
Joined
Feb 3, 2017
Messages
13
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,488
Office Version
365
Platform
Windows
Glad we could help & thanks for the feedback
 

Forum statistics

Threads
1,078,394
Messages
5,339,939
Members
399,340
Latest member
JasonT903

Some videos you may like

This Week's Hot Topics

Top