Save as PDF using button

[justundertall]

Good Day,

I would like to create a button that saves a pdf to specific folder based on the customer name.
also i would like the file name to be a combination of the info that is in the cells.

for example:
this is what i have currently on my spreadsheet that i want waved as pdf.

i would want the pdf to be saved to the file C:\Users\-Len\Desktop\Diverse\Customer Estimates\(company Name)
keep in mind the customer name would always change on the pdf which means i would want it to save to a different folder based off of the customer name.
and then i would want the file name to be Jackjack J1234 2021-0057 (company name, Job name, Estimate number)

is this possible? or am i asking for a miracle.

if it cant be saved to a specific folder based off of the company thats fine i would want it to go to C:\Users\-Len\Desktop\Diverse\Customer Estimates

thanks for the help.

 

[footoo]

Assuming a folder with the Company Name already exists, try this

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "C:\Users\-Len\Desktop\Diverse\Customer Estimates\" _
    & [N4] & "\" & [N4] & Chr(32) & [N6] & Chr(32) & [N8] & ".pdf"

 

[justundertall]

Assuming a folder with the Company Name already exists, try this

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "C:\Users\-Len\Desktop\Diverse\Customer Estimates\" _
    & [N4] & "\" & [N4] & Chr(32) & [N6] & Chr(32) & [N8] & ".pdf"

Awesome!!!! Thanks!
I am seeing issues in the future with my ideas here.
What do I need to change to make it always just save to the Customer Estimates folder?
Like you mentioned the folder may not always be there and I can see this causing some major issues.
thanks

 

[justundertall]

I was able to figure out the file save.
Thanks

 

[justundertall]

if I save the file twice is there a way to make save with the same file name but with a (1) or (2) behind it depending on how many times it saves?

Jackjack J1234 2021-0057 (original)
Jackjack J1234 2021-0057 (1)
Jackjack J1234 2021-0057 (2)

 

[footoo]

The macro and function below should add a folder with the Company Name if the folder does not exist, and add a number suffix to the saved workbooks
Make sure the path I have used is correct.

Sub Save_As_PDF()
Dim nbr As String, n%, fileName$, sFolderPath$, oFSO As Object
sFolderPath = "C:\Users\-Len\Desktop\Diverse\Customer Estimates\" & [N4] & "\"
If Dir(sFolderPath) <> "" Then GoTo e
MkDir sFolderPath
e: Application.DisplayAlerts = False
On Error Resume Next
Do While FileExists(fileName)
    fileName = sFolderPath & [N4] & "\" & [N4] & Chr(32) & [N6] & Chr(32) & [N8] & nbr & ".pdf"
    n = n + 1
    nbr = " (" & n & ")"
Loop
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, fileName:=fileName
On Error GoTo 0
Application.DisplayAlerts = True
End Sub

Private Function FileExists(fname) As Boolean
Dim x As String: x = Dir(fname)
If x <> "" Then FileExists = True Else: FileExists = False
End Function

 

[footoo]

Amended :

Sub Save_As_PDF()
Dim nbr As String, n%, fileName$, sFolderPath$
sFolderPath = "C:\Users\-Len\Desktop\Diverse\Customer Estimates\" & [N4] & "\"
If Dir(sFolderPath) <> "" Then GoTo e
MkDir sFolderPath
e: Do While FileExists(fileName)
    fileName = sFolderPath & [N4] & Chr(32) & [N6] & Chr(32) & [N8] & nbr & ".pdf"
    n = n + 1
    nbr = " (" & n & ")"
Loop
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, fileName:=fileName
End Sub

Private Function FileExists(fname) As Boolean
If Dir(fname) <> "" Then FileExists = True Else: FileExists = False
End Function

 

[justundertall]

THANKS so much for the reply. it half works lol
I'm going to try and explain this the easiest way.

When I run the code it creates the file folder based on the customer name, which is completely wicked!!
It doesn't seem to be saving the file to the folder it created?

Am i missing something? maybe the path we have is wrong?

 

[footoo]

It works for me. I tested as follows :

Sub Save_As_PDF()
Dim nbr As String, n%, fileName$, sFolderPath$
sFolderPath = "D:\Documents\" & [N4] & "\"
If Dir(sFolderPath) <> "" Then GoTo e
MkDir sFolderPath
e: Do While FileExists(fileName)
    fileName = sFolderPath & [N4] & Chr(32) & [N6] & Chr(32) & [N8] & nbr & ".pdf"
    n = n + 1
    nbr = " (" & n & ")"
Loop
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, fileName:=fileName
End Sub

Check the path is correct :
"C:\Users\-Len\Desktop\Diverse\Customer Estimates\"

 

[justundertall]

Quoting Spreadsheet-1.0.xlsm
	M	N	O	P	Q
1
2
3
4	Company Name:	JACK
5	Contact Name:	JACK
6	Job Name:	J1234
7	Date:(m/d/y)	3/26/2021
8	Estimate Number:	2021-0059
9
10			Job Materials & Labour: $8,016.00
11
12	2 - 16'0" x 16'0" 3717 Door & Hardware 1 Row 24" x 12" Sealed Unit Insulated glass Standard Lift Pusher Springs Full Tapered reverse angle tracks Full Perimeter Weatherstrip Chain Hoist
13
14
15
16
17
18
19
20				Materials & Labour: $8,016.00
Customer Estimate

Cell Formulas
Range	Formula
M4:M7	M4	='Estimate Details'!B1
N8,N4:N6	N4	=UPPER('Estimate Details'!E1)
N7	N7	=IF('Estimate Details'!E4>0,'Estimate Details'!E4,"")
O10	O10	=IF(AND('Estimate Details'!$A$2="Yes",'Job Summary'!B15>0),'Job Summary'!B15,"")
M12	M12	=IF('Estimate Details'!B9>0,( G12&CHAR(10)& G13&CHAR(10)& G14&CHAR(10)& G15&CHAR(10)& G16&CHAR(10)& G17&CHAR(10)& G18&CHAR(10)), "")
P20	P20	=IF(AND('Estimate Details'!$A$4="Yes",'Job Summary'!B13>0),'Job Summary'!B13,"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
M20:N20	Expression	=$M$12>""	text	NO
O20	Expression	=$M$12>""	text	NO
M10:N10	Expression	=$O$10<""	text	NO
O10:P10	Expression	=$O$10<""	text	NO
P20	Cell Value	top 10 values	text	NO
P20	Expression	=$M$12>""	text	NO
M12:P19	Expression	=$M$12>""	text	NO

I have attached the workbook
I still can only create the folder and I don't get the pdf.

Here is what I have.
This is the file path: C:\Users\-Len\Desktop\Diverse\Customer Estimates

Sub Save_As_PDF()
Dim nbr As String, n%, fileName$, sFolderPath$, oFSO As Object
sFolderPath = "C:\Users\-Len\Desktop\Diverse\Customer Estimates" & [N4] & "\"
If Dir(sFolderPath) <> "" Then GoTo e
MkDir sFolderPath
e: Application.DisplayAlerts = False
On Error Resume Next
Do While FileExists(fileName)
fileName = sFolderPath & [N4] & "\" & [N4] & Chr(32) & [N6] & Chr(32) & [N8] & nbr & ".pdf"
n = n + 1
nbr = " (" & n & ")"
Loop
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, fileName:=fileName
On Error GoTo 0
Application.DisplayAlerts = True
End Sub

Private Function FileExists(fname) As Boolean
Dim x As String: x = Dir(fname)
If x <> "" Then FileExists = True Else: FileExists = False
End Function