VBA Save as PDF not working anymore

Copper Contributor

Hello i had a vba excel which print automatically a pdf (save as pdf) after generating some data but now it’s not printing any pdf could you please help me ?

2 Replies

@MicrosoftNewbie121 

I have been using this macro for a long time to export cell ranges into pdf and create a path to save it.

try this, and maybe you can get an idea.

Sub SaveRangeAsPDF()
    Dim ws As Worksheet
    Dim wsTrend As Worksheet
    Dim rng As Range
    Dim strPath As String
    Dim strYearFolder As String
    Dim strMonthFolder As String
    Dim strFileName As String
    Dim strDate As String
    Dim rngDate As Range
    
    ' Set the worksheets and range
    Set ws = ThisWorkbook.Sheets("TEMPLATE")
    Set wsTrend = ThisWorkbook.Sheets("Trend Report")
    Set rng = ws.Range("C1:AB130")
    
    ' Check if the date in E5 exists in the Trend Report sheet's column A
    Set rngDate = wsTrend.Range("A:A").Find(ws.Range("E5").Value, LookIn:=xlValues)
    If rngDate Is Nothing Then
        MsgBox "There is no data for the date " & ws.Range("E5").Value & ".", vbExclamation, "No Data"
        Exit Sub
    End If
    
    ' Set the main path
    strPath = "Z:\Production\2024\"
    
    ' Check if the year folder exists, if not create it
    strYearFolder = strPath & Year(Date) & " PRODUCTION MD TREND MONITORING\"
    If Dir(strYearFolder, vbDirectory) = "" Then MkDir strYearFolder
    
    ' Check if the month folder exists, if not create it
    strMonthFolder = strYearFolder & Format(ws.Range("E5").Value, "mmmm") & " MD TEMPERATURE TREND MONITORING\"
    If Dir(strMonthFolder, vbDirectory) = "" Then MkDir strMonthFolder
    
    ' Set the file name
    strDate = Format(ws.Range("E5").Value, "mm-dd-yyyy")
    strFileName = strMonthFolder & strDate & " MD TEMP. TREND MONITORING.pdf"
    
    ' Unhide the worksheet temporarily
    ws.Visible = xlSheetVisible
    
    ' Export the range as PDF
    On Error GoTo ErrorHandler
    rng.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    MsgBox "The conversion is finished. The PDF has been saved at: " & strFileName, vbInformation, "Success"
    
    ' Hide the worksheet again
    ws.Visible = xlSheetVeryHidden
    Exit Sub
    
ErrorHandler:
    MsgBox "There was an error. Please check if the file location or the shared folder is connected.", vbCritical, "Error"
    ' Hide the worksheet again
    ws.Visible = xlSheetVeryHidden
End Sub

 

@Rr_could you pease share the excel file if it's possible ? i would like to test it

also the macro is working for me i can print to pdf but other users can't