TobiaLeviathon
New Member
- Joined
- Mar 3, 2017
- Messages
- 2
Hi Guys,
I have been working on saving my Invoices in the raw excel format that allows macros, xlsm, and in pdf format to the same folder, having it named automatically by the value of two cells, invoice number and customer name, then printing it out and closing the workbook down. It took a lot of searching to combine these three things, so I thought I'd share it to help anyone who needs it....
This is VBA, I assume you have already 'Inserted' a 'button' from the 'activeX controls' in the 'developer' tab (search google for instructions on this if not)
Here's the code- (bold is the code you need, italics are comments to help understand the code)
Sub SaveandPrint() 'this is the macro's name
Dim FileName As String
Dim Path As String
ActiveSheet.PrintOut 'this bit prints the sheet
Application.DisplayAlerts = False
Path = "C:\add\your\file\destination\here" 'Change the directory path here where you want to save the file
FileName = Range("C18").Value & " " & Range("A7").Value & ".xlsm" 'Change extension here for different excel formats.
'Also, change range cell values to select different cell value for naming
ActiveWorkbook.SaveAs Path & FileName, xlOpenXMLWorkbookMacroEnabled 'Change the format here which matches with the extension above. 'Choose from the following link http://msdn.microsoft.com/en-us/library/office/ff198017.aspx
Application.DisplayAlerts = True
Dim fName As String 'this is the saving to pdf bit
fName = Range("C18").Value & " " & Range("A7").Value 'again, change cells to C18 and A7 to the ones you want to name file
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
"C:\add\your\file\destination\here" & fName, Quality:=xlQualityStandard, _ 'change destination folder
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
ActiveWorkbook.Close 'closes the workbook
End Sub
Hope this helps and save some lots of time, took me bloody ages to wrap my head round it and implement it!
I have been working on saving my Invoices in the raw excel format that allows macros, xlsm, and in pdf format to the same folder, having it named automatically by the value of two cells, invoice number and customer name, then printing it out and closing the workbook down. It took a lot of searching to combine these three things, so I thought I'd share it to help anyone who needs it....
This is VBA, I assume you have already 'Inserted' a 'button' from the 'activeX controls' in the 'developer' tab (search google for instructions on this if not)
Here's the code- (bold is the code you need, italics are comments to help understand the code)
Sub SaveandPrint() 'this is the macro's name
Dim FileName As String
Dim Path As String
ActiveSheet.PrintOut 'this bit prints the sheet
Application.DisplayAlerts = False
Path = "C:\add\your\file\destination\here" 'Change the directory path here where you want to save the file
FileName = Range("C18").Value & " " & Range("A7").Value & ".xlsm" 'Change extension here for different excel formats.
'Also, change range cell values to select different cell value for naming
ActiveWorkbook.SaveAs Path & FileName, xlOpenXMLWorkbookMacroEnabled 'Change the format here which matches with the extension above. 'Choose from the following link http://msdn.microsoft.com/en-us/library/office/ff198017.aspx
Application.DisplayAlerts = True
Dim fName As String 'this is the saving to pdf bit
fName = Range("C18").Value & " " & Range("A7").Value 'again, change cells to C18 and A7 to the ones you want to name file
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
"C:\add\your\file\destination\here" & fName, Quality:=xlQualityStandard, _ 'change destination folder
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
ActiveWorkbook.Close 'closes the workbook
End Sub
Hope this helps and save some lots of time, took me bloody ages to wrap my head round it and implement it!
