Save as PDF using button

justundertall

New Member
Joined
Mar 22, 2021
Messages
21
Office Version
  1. 2013
Platform
  1. Windows
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.
1616469011915.png


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.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,239
Office Version
  1. 2016
Platform
  1. Windows
Assuming a folder with the Company Name already exists, try this
VBA Code:
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "C:\Users\-Len\Desktop\Diverse\Customer Estimates\" _
    & [N4] & "\" & [N4] & Chr(32) & [N6] & Chr(32) & [N8] & ".pdf"
 
Solution

justundertall

New Member
Joined
Mar 22, 2021
Messages
21
Office Version
  1. 2013
Platform
  1. Windows
Assuming a folder with the Company Name already exists, try this
VBA Code:
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

New Member
Joined
Mar 22, 2021
Messages
21
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Sep 21, 2016
Messages
3,239
Office Version
  1. 2016
Platform
  1. Windows
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.
VBA Code:
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

Well-known Member
Joined
Sep 21, 2016
Messages
3,239
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Amended :
VBA Code:
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

New Member
Joined
Mar 22, 2021
Messages
21
Office Version
  1. 2013
Platform
  1. Windows
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?
1616783533744.png


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

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,239
Office Version
  1. 2016
Platform
  1. Windows
It works for me. I tested as follows :
VBA Code:
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

New Member
Joined
Mar 22, 2021
Messages
21
Office Version
  1. 2013
Platform
  1. Windows
Cell Formulas
RangeFormula
M4:M7M4='Estimate Details'!B1
N8,N4:N6N4=UPPER('Estimate Details'!E1)
N7N7=IF('Estimate Details'!E4>0,'Estimate Details'!E4,"")
O10O10=IF(AND('Estimate Details'!$A$2="Yes",'Job Summary'!B15>0),'Job Summary'!B15,"")
M12M12=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)), "")
P20P20=IF(AND('Estimate Details'!$A$4="Yes",'Job Summary'!B13>0),'Job Summary'!B13,"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
M20:N20Expression=$M$12>""textNO
O20Expression=$M$12>""textNO
M10:N10Expression=$O$10<""textNO
O10:P10Expression=$O$10<""textNO
P20Cell Valuetop 10 valuestextNO
P20Expression=$M$12>""textNO
M12:P19Expression=$M$12>""textNO


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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,407
Messages
5,641,955
Members
417,249
Latest member
serrulate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top