Excel Button to Save as PDF

[MattQAQC]

Hi,

i have a button on my spreadsheet to save the sheet as a PDF but i can figure out how to get it to create a copy of the file in excel with the same name and save both that copy and the PDF in the location of where the file original file is saved

this is my code currently:

Private Sub CommandButton2_Click()
Application.ScreenUpdating = False
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=Range("P2"), _
OpenAfterPublish:=True
Application.ScreenUpdating = True
End Sub

 

[scoudny]

Hi Matt

This what i use at the moment

Private Sub CommandButton1_Click()

FN = Range("Save to") & Range("Save as")
FN_PDF = FN & ".pdf"
FN_XLSM = FN & ".xlsm"


'Save as a PDF
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=FN_PDF, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=False, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True

Application.DisplayAlerts = False 'Set False, so not warned about over writing the files.

'Save as a macro enabled Excel file (e.g. *.xlsm)
ActiveWorkbook.SaveAs Filename:=FN_XLSM, _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

Application.DisplayAlerts = True 'Set back to true if needed...


End Sub

Works well for me but interested to see what others come up with.
My version doesnt like saving with the same name as already open.

 

[MattQAQC]

Hi Matt

This what i use at the moment

Private Sub CommandButton1_Click()

FN = Range("Save to") & Range("Save as")
FN_PDF = FN & ".pdf"
FN_XLSM = FN & ".xlsm"


'Save as a PDF
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=FN_PDF, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=False, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True

Application.DisplayAlerts = False 'Set False, so not warned about over writing the files.

'Save as a macro enabled Excel file (e.g. *.xlsm)
ActiveWorkbook.SaveAs Filename:=FN_XLSM, _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

Application.DisplayAlerts = True 'Set back to true if needed...


End Sub

Works well for me but interested to see what others come up with.
My version doesnt like saving with the same name as already open.

Thanks mate.

where is the file location on your code or does it not need it?

i have it set currently to save as a file name in a certain cell. do you know what i would chnage in yours to keep that or what to just add to mine to save in same file location?

 

[MattQAQC]

1-Quotation Blank NEW IES.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N						T	U	V
1
2			QUOTATION					Customer:
3								Job Ref:
4			Quote No:			85476		Project Length:
5			Date:	21/07/2022
6			By:						NET TOTAL =		£0.00
7										VAT =	£0.00
8									TOTAL =		£0.00
9	Item					Size			Unit Net	Qty	Net Total
10											£0.00
11											£0.00
12											£0.00
13											£0.00
14											£0.00
15											£0.00
16											£0.00
17											£0.00
18											£0.00
19											£0.00
20											£0.00
21											£0.00
22											£0.00
23											£0.00
24											£0.00
25											£0.00
26											£0.00
27											£0.00
28											£0.00
29											£0.00
30											£0.00
31											£0.00
32											£0.00
33											£0.00
34											£0.00
35											£0.00
36											£0.00
37											£0.00
38											£0.00
39											£0.00
40											£0.00
41											£0.00
42											£0.00
43											£0.00
44											£0.00
45											£0.00
46											£0.00
47											£0.00
48											£0.00
49											£0.00
50
51
52
53
54
55
56
57
Quote

Cell Formulas
Range	Formula
E4	E4	=LEFT(I3,6)
F4	F4	=S5+D5
D5	D5	=NOW()
K6	K6	=SUM((K10:K15)+(K17:K22)+(K24:K29))
K7	K7	=K6*0.2
K8	K8	=K6+K7
K10:K49	K10	=SUM(I10*J10)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K10:K49	Cell Value	contains "0"	text	NO

 

[scoudny]

Hi Matt, in the row FN = Range("Save to") & Range("Save as"), enter the cell with you save location in place of "Save to" and the cell with your document title inplace of "Save to"
So if the address you wanted to save the document to was in cell A1 and the title you wanted to save it as was in cell A2 it would be FN = Range(A1) & Range(A2)
Don't forget the \ at the end of the address.
Hope this helps