VBA - Custom Views to PDF

[JDeMaro22]

Hello,

I am looking for help on my macro to convert multiple custom views into PDF's and save them in a particular folder. My macro currently will convert each of the custom views into a pdf however I need help with saving them into a folder and with the naming convention.

I would like all of my custom views to be saved as such: Dr. Goodman - MAR 22 comp calculation
- I need help with VBA to add the previous months name and last 2 digits of the year

I also would like these files to be saved into the following folder: \\fcs.com\department\finance\Budget\Regional Reporting FCS\Adam Derringer\region 9 comp pdfs

Please let me know if you have any questions,

Josh

Sub EXCEL_TO_PDF()

'

ActiveWorkbook.CustomViews("GOODMAN").Show

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="demo.pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=True

ActiveWorkbook.CustomViews("GERSTEN").Show

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="demo2.pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=True

'etc

End Sub

[HansV]

Try this:

Code: Select all

Sub EXCEL_TO_PDF()
    ' Folder path with trailing backslash
    Const strPath = "\\fcs.com\department\finance\Budget\Regional Reporting FCS\Adam Derringer\region 9 comp pdfs\"
    ' Month part of the filename
    Dim strMonth As String
    ' Array to hold names
    Dim arrNames() As Variant
    ' Loop variable for name
    Dim varName As Variant
    ' File name
    Dim strFile As String
    ' Last month
    strMonth = UCase(Format(Date - Day(Date), "mmm yy"))
    ' Array of names
    arrNames = Array("GOODMAN", "GERSTEN", "...")
    For Each varName In arrNames
        ActiveWorkbook.CustomViews(varName).Show
        strFile = "Dr. " & varName & " " & strMonth & " comp calculation.pdf"
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strPath & strFile, Quality:=xlQualityStandard, _
            IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=True
    Next varName
End Sub

[JDeMaro22]

Thank you Hans, this works perfectly.