Excel Macro - Save as PDF

[bdenn]

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:

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]

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]

If you want to select the folder, try this:

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]

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]

Hello,
I will try this out and see thanks.

 

[bdenn]

If you want to select the folder, try this:

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]

If you always want to use that folder try

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.

 

[bdenn]

If you always want to use that folder try

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]

Glad we could help & thanks for the feedback