VBA Button to save as PDF returning run-time error

[RJSIGKITS]

Hi Guys.

I am trying to make a button that saves the current sheet as a PDF within a directory that is created by clicking a button previously.
So, on my sheet, I have 2 buttons, the 1st creates a filepath with a name according to a reference on the sheet, and saves the excel document.
I now need a second button which saves a PDF of the current sheet into that new directory, but no matter what I try, I keep getting errors...


This is what I have, but the 'SavePDF_Click()' Sub is returning an error runtime 1004 -

Private Sub Save1_Click()Dim Path As String
Dim FileName1 As String
Path = "C:\Users\Richard\Box Sync\Default Sync Folder\4. Richard Johns Design\Web Design\Clients\Complete Flooring"
FileName1 = Range("H5")
If Dir(Path & "\" & FileName1, vbDirectory) = "" Then
    MkDir (Path & "\" & FileName1)
    MkDir (Path & "\" & FileName1 & "\Quotes")
    MkDir (Path & "\" & FileName1 & "\Photos")
    MkDir (Path & "\" & FileName1 & "\Photos" & "\Before")
    MkDir (Path & "\" & FileName1 & "\Photos" & "\Progress")
    MkDir (Path & "\" & FileName1 & "\Photos" & "\After")
    MkDir (Path & "\" & FileName1 & "\Invoices")
    MkDir (Path & "\" & FileName1 & "\Invoices" & "\Paid")
    MsgBox "Success!" & vbCr & "A New Client Record has been created and saved successfully", vbExclamation + vbOKOnly, "Success!"
    ThisWorkbook.Sheets("Sales Order").Activate
    Else
     If MsgBox("A Record Already Exists for this Client" & vbCr & "Click 'OK' to save changes to this record", vbExclamation + vbOKOnly, "Error") = vbOK Then
     ThisWorkbook.Save
    ThisWorkbook.Sheets("Sales Order").Activate
    Exit Sub
    End If
End If
ActiveWorkbook.SaveAs Filename:=Path & "\" & FileName1 & "\" & FileName1 & ".xlsm", FileFormat:=52
End Sub


Private Sub SavePDF_Click()
ThisWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path & "\" & FileName1 & "\" & FileName1 & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End Sub

 

[Fishboy]

Hi Guys.

I am trying to make a button that saves the current sheet as a PDF within a directory that is created by clicking a button previously.
So, on my sheet, I have 2 buttons, the 1st creates a filepath with a name according to a reference on the sheet, and saves the excel document.
I now need a second button which saves a PDF of the current sheet into that new directory, but no matter what I try, I keep getting errors...


This is what I have, but the 'SavePDF_Click()' Sub is returning an error runtime 1004 -

Private Sub Save1_Click()Dim Path As String
Dim FileName1 As String
Path = "C:\Users\Richard\Box Sync\Default Sync Folder\4. Richard Johns Design\Web Design\Clients\Complete Flooring"
FileName1 = Range("H5")
If Dir(Path & "\" & FileName1, vbDirectory) = "" Then
    MkDir (Path & "\" & FileName1)
    MkDir (Path & "\" & FileName1 & "\Quotes")
    MkDir (Path & "\" & FileName1 & "\Photos")
    MkDir (Path & "\" & FileName1 & "\Photos" & "\Before")
    MkDir (Path & "\" & FileName1 & "\Photos" & "\Progress")
    MkDir (Path & "\" & FileName1 & "\Photos" & "\After")
    MkDir (Path & "\" & FileName1 & "\Invoices")
    MkDir (Path & "\" & FileName1 & "\Invoices" & "\Paid")
    MsgBox "Success!" & vbCr & "A New Client Record has been created and saved successfully", vbExclamation + vbOKOnly, "Success!"
    ThisWorkbook.Sheets("Sales Order").Activate
    Else
     If MsgBox("A Record Already Exists for this Client" & vbCr & "Click 'OK' to save changes to this record", vbExclamation + vbOKOnly, "Error") = vbOK Then
     ThisWorkbook.Save
    ThisWorkbook.Sheets("Sales Order").Activate
    Exit Sub
    End If
End If
ActiveWorkbook.SaveAs Filename:=Path & "\" & FileName1 & "\" & FileName1 & ".xlsm", FileFormat:=52
End Sub


Private Sub SavePDF_Click()
ThisWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path & "\" & FileName1 & "\" & FileName1 & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End Sub

Hi RJSIGKITS,

I suspect that is because you have not dim'd or defined FileName1 or path in the second macro. If you add these lines to the start of it I think it should resolve the issue:

Private Sub SavePDF_Click()
Dim FileName1 As String
Dim Path As String
Path = "C:\Users\Richard\Box Sync\Default Sync Folder\4. Richard Johns Design\Web Design\Clients\Complete Flooring"
FileName1 = Range("H5")
ThisWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path & "\" & FileName1 & "\" & FileName1 & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End Sub

 

[RJSIGKITS]

Hi RJSIGKITS,

I suspect that is because you have not dim'd or defined FileName1 or path in the second macro. If you add these lines to the start of it I think it should resolve the issue:

Private Sub SavePDF_Click()
Dim FileName1 As String
Dim Path As String
Path = "C:\Users\Richard\Box Sync\Default Sync Folder\4. Richard Johns Design\Web Design\Clients\Complete Flooring"
FileName1 = Range("H5")
ThisWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path & "\" & FileName1 & "\" & FileName1 & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End Sub

Perfect, thank you - I knew it was something STUPID I was missing!
You're a diamond!

 

[Fishboy]

Perfect, thank you - I knew it was something STUPID I was missing!
You're a diamond!

No worries. The simplest of mistakes are the easiest to make and be blind to. Especially if in your module both codes are one after the other, it is easy to forget that what has been declared in one code has not been declared in the other.

Anyway, I'm glad you got it working. Thanks for the positive feedback.