Home > Back-end >  I am trying to use VBA to Print to PDF and am receiving a 1004 runtime Error
I am trying to use VBA to Print to PDF and am receiving a 1004 runtime Error

Time:07-12

Could someone sell me why I am getting a runtime error here?? I have almost this identical code in another project that works, and I cant figure out the issue.

Sub Create_PDF()
' Create and save .pdf
Dim pdfName As String
Dim myrange As String

myrange = Cells(Rows.Count, 6).End(xlUp).Address
Dim AccountNumber As String

AccountNumber = Right(A1, 3)
FullName = "P:\Public\Generated Letters\LTXN Export Spreadsheets\" & "AccountEnding" & AccountNumber & ".pdf"
'Sets the name and location for the new file


myrange = Cells(Rows.Count, 6).End(xlUp).Address

'sets the string end for the print area


With ActiveSheet.PageSetup
        .PrintArea = "A1:" & myrange
           .Orientation = xlLandscape
           .Zoom = False
                   .FitToPagesTall = False
                   .FitToPagesWide = 1
End With
'Setting the spreadsheet to print active content with columns fit to single page


If Dir(FullName) <> vbNullString Then

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="P:\Public\Generated_Letters\LTXN_Export_Spreadsheets\" & "AccountEnding" & AccountNumber & " - " & Format(Now, "mm.dd.yyyy hh mm") & ".pdf" _
, Quality:=xlQualityMedium, IncludeDocProperties:=False, _
IgnorePrintAreas:=False, OpenAfterPublish:=True
Else

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="P:\Public\Generated_Letters\LTXN_Export_Spreadsheets\" & "AccountEnding" & AccountNumber & Format(Now, "mm.dd.yyyy hh mm") & ".pdf" _
, Quality:=xlQualityMedium, IncludeDocProperties:=False, _
IgnorePrintAreas:=False, OpenAfterPublish:=True
End If 

'###This is where I am getting the runtime error and the file is not saving###

End Sub

Sub openFolder()
'Open the folder that we save the PDF to

     Call Shell("explorer.exe" & " " & "P:\Public\Generated Letters\LTXN Export Spreadsheets\", vbNormalFocus)
End Sub

The one difference from the other project is that AccountNumber is a number and not text, but I figured in defining it as a string it shouldnt matter???

CodePudding user response:

Try this:

Option Explicit

'use Const for fixed values
Const EXPORTS As String = "P:\Public\Generated Letters\LTXN Export Spreadsheets\"

Sub Create_PDF()
    
    Dim ws As Worksheet, myRange As Range
    Dim AccountNumber As String, dt As String, FullName As String, fName As String, sep As String
    
    Set ws = ActiveSheet
    AccountNumber = Right(ws.Range("A1").Value, 3) 'not just `A1`
    
    With ActiveSheet.PageSetup
        .PrintArea = "A1:" & ws.Cells(Rows.Count, 6).End(xlUp).Address
        .Orientation = xlLandscape
        .Zoom = False
        .FitToPagesTall = False
        .FitToPagesWide = 1
    End With
    
    dt = Format(Now, "mm.dd.yyyy hh mm")
    fName = EXPORTS & "AccountEnding" & AccountNumber
    
    If Len(Dir(fName & ".pdf")) > 0 Then sep = " - "
    fName = fName & sep & dt & ".pdf"
    
    'note there's no `xlQualityMedium` enumeration for `Quality`
    ws.ExportAsFixedFormat Type:=xlTypePDF, fileName:=fName, _
        Quality:=xlQualityStandard, IncludeDocProperties:=False, _
        IgnorePrintAreas:=False, OpenAfterPublish:=True
    
End Sub

Sub openFolder()
    'Folder paths with spaces need to be quoted....
    Call Shell("explorer.exe" & " """ & EXPORTS & """", vbNormalFocus)
End Sub

  • Related