1

trying to make macro for active sheet conversion to pdf. I found the code on internet and made little adjustments. Macro is working and extracting active sheet to pdf, but problem come with file name. It doesn't name it after cell SS5(this cell has formula), but we change SS5 to A2, it works. So where is the problem?

This is code section where I think problem is:

CompanyN = Range("SS5").Value     
ThisFile = ActiveWorkbook.Name     
PathName = ActiveWorkbook.Path     
SvAs = "Invoice - " & CompanyN & " - " & " (payment)" & ".pdf"

enter image description here

Formula in cell SS5:

 =E8&" - "&IFERROR(LEFT(MID(A4,11+1,LEN(A4)),FIND("_",MID(A4,11+1,LEN(A4)))-1),"Įrašytį serijos numerį")&SUMPRODUCT(MID(0&A4, LARGE(INDEX(ISNUMBER(--MID(A4, ROW(INDIRECT("1:"&LEN(A4))), 1)) * ROW(INDIRECT("1:"&LEN(A4))), 0), ROW(INDIRECT("1:"&LEN(A4))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(A4)))/10)

FULL CODE:

    Sub PrintPDF()
    
    Call Save_PDF
    
    End Sub
    
    Function Save_PDF() As Boolean
    
    ' Copies sheets into new PDF file for e-mailing
    Dim Thissheet As String, ThisFile As String, PathName As String
    Dim SvAs As String
    Application.ScreenUpdating = False
    
    ' Get File Save Name
    CompanyN = Range("A2").Value
    ThisFile = ActiveWorkbook.Name
    PathName = ActiveWorkbook.Path
    SvAs = "Invoice - " & CompanyN & " - " & " (payment)" & ".pdf"
    
    'Set Print Quality 
    On Error Resume Next
    ActiveSheet.PageSetup.PrintQuality = 600
    Err.Clear
    On Error GoTo 0
    
    ' Instruct user how to send
    On Error GoTo RefLibError
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:=SvAs, Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=True
    On Error GoTo 0
    
    SaveOnly:
    MsgBox "A copy of this sheet has been successfully saved as a  .pdf  file: " & vbCrLf & vbCrLf & SvAs & _
    "Review the .pdf document. If the document does NOT look good, adjust your printing parameters, and try again."
    
    Save_PDF = True
    GoTo EndMacro
    
    RefLibError:     MsgBox "Unable to save as PDF. Reference library not found."
    
    Save_PDF = False
    EndMacro:
    
    End Function
braX
  • 11,506
  • 5
  • 20
  • 33
Vokoli S
  • 11
  • 2
  • 2
    How does VBA name the file when it fails? – Foxfire And Burns And Burns Mar 29 '23 at 08:32
  • a) Fully qualify your range references; b) if you intend to use `Thissheet` assign a value to this variable and use it for your references, c) prefix your filename with the actual path ... – T.M. Mar 29 '23 at 08:45
  • When vba fails, it doesnt execute extraction, and just write message from RefLibError: MsgBox "Unable to save as PDF. Reference library not found." . Tried deleting this part, after that VBA says [ compile error: label not defined ]. – Vokoli S Mar 29 '23 at 13:08

0 Answers0