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.
 

justundertall

New Member
Joined
Mar 22, 2021
Messages
21
Office Version
  1. 2013
Platform
  1. Windows
if I run the macro and run it a second time I get the an error; I run the debug and it highlights this.

1616810882868.png
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,229
Office Version
  1. 2016
Platform
  1. Windows
You're using the wrong macro. Use the amended one in post #7.
 

justundertall

New Member
Joined
Mar 22, 2021
Messages
21
Office Version
  1. 2013
Platform
  1. Windows
You're using the wrong macro. Use the amended one in post #7.
oh wow now i just feel silly. I am so sorry.
So that one works perfectly!!! Thank you.
How do i do the same thing but saving as an excel workbook?
im assuming its gonna be the same code with some minor adjustments?
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,229
Office Version
  1. 2016
Platform
  1. Windows
Change this :
fileName = sFolderPath & [N4] & Chr(32) & [N6] & Chr(32) & [N8] & nbr & ".pdf"

To this :
fileName = sFolderPath & [N4] & Chr(32) & [N6] & Chr(32) & [N8] & nbr & ".xlsm"
 

justundertall

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

ADVERTISEMENT

So I did the change. But it gave me some issues.
I just have a couple more questions(in red) . I did try and fix it myself. Unfortunately, I just don't know quite enough about the VBA codes to figure it out ;p

Sub Save_As_xlsm() Is this correct?
Dim nbr As String, n%, fileName$, sFolderPath$
sFolderPath = "C:\Users\-Len\Desktop\Diverse\Customer Estimates\" & [E1] & "\"
If Dir(sFolderPath) <> "" Then GoTo e

e: Do While FileExists(fileName)
fileName = sFolderPath & [E1] & Chr(32) & [E3] & Chr(32) & [E5] & nbr & ".xlsm"
n = n + 1
nbr = " (" & n & ")"
Loop
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, fileName:=fileName There is an issue here as well. I believe its the type. I tried a few different things and I can't seem to get it. Sorry :(
End Sub

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

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,229
Office Version
  1. 2016
Platform
  1. Windows
Try :
VBA Code:
Sub Save_Sheet_In_New_wb()
Dim nbr As String, n%, fileName$, sFolderPath$, shName$
shName = ActiveSheet.Name
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 & ".xlsx"
    n = n + 1
    nbr = " (" & n & ")"
Loop
Dim wb As Workbook: Set wb = Workbooks.Add
ThisWorkbook.ActiveSheet.Copy Before:=wb.Sheets(1)
wb.SaveAs fileName
End Sub

You will probably need to convert the formulas to constants . If so, add this line at the end of the macro :
VBA Code:
ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
 

justundertall

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

ADVERTISEMENT

when I run the debugger it's highlighting
Sub Save_Sheet_In_New_wb()

I also don't know anything about converting formulas to constants.
Will that wreck my formulas or formatting or settings?
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,229
Office Version
  1. 2016
Platform
  1. Windows
Try changing the macro name to something else (any error message displayed?)

Converting to values will remove the formulas in the new workbook.
Otherwise some formulas will contain links to the original workbook, which means the new wb will be updated for any changes to the linked cells in the original wb.
It's up to you.
 

justundertall

New Member
Joined
Mar 22, 2021
Messages
21
Office Version
  1. 2013
Platform
  1. Windows
I'm having issues with this code. I have put this on a newer computer so i assume that the version of excel is newer.
the part that is giving me issues is ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, fileName:=fileName

This is what I currently have.


Sub Save_As_PDF()
Dim nbr As String, n%, fileName$, sFolderPath$
sFolderPath = "C:\Users\Len Girard\Desktop\Estimates" & [N4] & "\"
If Dir(sFolderPath) <> "" Then GoTo e

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


this is the error i get when i select the save as pdf button


1618243196143.png
 

Watch MrExcel Video

Forum statistics

Threads
1,129,724
Messages
5,637,997
Members
416,998
Latest member
bbowne

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