Microsoft Office Reference

---

Autosave as PDF and DOCX

Use this code to create a new macro and quick access button to automatically save files as docx and pdf (updates the document without overwriting the original Word save functions) – 1 2:

' Create New Function PDFandSave
 Public Sub PDFandSaveWord()

ActiveDocument.Save
 SaveActiveDocumentAsPdfWord

End Sub

Sub SaveActiveDocumentAsPdfWord()

Dim strPath As String

On Error GoTo Errhandler

If InStrRev(ActiveDocument.FullName, ".") <> 0 Then

strPath = Left(ActiveDocument.FullName, InStrRev(ActiveDocument.FullName, ".") - 1) & ".pdf"

ActiveDocument.SaveAs FileName:=strPath, FileFormat:=wdFormatPDF
 End If

On Error GoTo 0

Exit Sub

Errhandler:

MsgBox "There was an error saving a copy of this document as PDF. " & _
 "Ensure that the PDF is not open for viewing and that the destination path is writable. Error code: " & Err

End Sub

 

Steps:

1. Add Macro: View > Macros> View Macros > (Project Name=PDFandSave) > (Macros in Normal.dotm) > Create
2. Select all and replace with code above.
3. Rename Macro Project as PDFandSave
4. Customize Quick Access Toolbar > More Commands > Choose Commands from Macros > Normal.PDFandSave.PDFandSave > Add > Modify > (Change Icon and Name) > OK

 

Autosave as PDF and XLSX

Use this code to create a new macro and quick access button to automatically save files as xlsx and pdf (updates the document without overwriting the original Excel save functions) – 1 2 3 4 5 6 7:

' Create New Function PDFandSave(Active Sheet)
 Public Sub PDFandSaveExcelSheet()

ActiveWorkbook.Save
 SaveActiveDocumentAsPdfExcelSheet

End Sub

' Create New Function PDFandSave(Entire WB)
 Public Sub PDFandSaveExcelWB()

ActiveWorkbook.Save
 SaveActiveDocumentAsPdfExcelWB

End Sub

Sub SaveActiveDocumentAsPdfExcelSheet()

Dim strPath As String

On Error GoTo Errhandler

If InStrRev(ActiveWorkbook.FullName, ".") <> 0 Then

strPath = Left(ActiveWorkbook.FullName, InStr(ActiveWorkbook.FullName, ".") - 1) & ".pdf"

ActiveSheet.ExportAsFixedFormat _
 Type:=xlTypePDF, _
 Filename:=strPath, _
 Quality:=xlQualityStandard, _
 IncludeDocProperties:=True, _
 IgnorePrintAreas:=False, _
 OpenAfterPublish:=True

End If

On Error GoTo 0

Exit Sub

Errhandler:

MsgBox "There was an error saving a copy of this document as PDF. " & _
 "Ensure that the PDF is not open for viewing and that the destination path is writable. Error code: " & Err

End Sub

Sub SaveActiveDocumentAsPdfExcelWB()

Dim strPath As String
 Dim sheetName As String
 Dim workSheet As workSheet

On Error GoTo Errhandler

If InStrRev(ActiveWorkbook.FullName, ".") <> 0 Then

strPath = Left(ActiveWorkbook.FullName, InStr(ActiveWorkbook.FullName, ".") - 1)

For Each workSheet In Worksheets

workSheet.Select
 sheetName = workSheet.Name

ActiveSheet.ExportAsFixedFormat _
 Type:=xlTypePDF, _
 Filename:=strPath & " - " & sheetName & ".pdf", _
 Quality:=xlQualityStandard, _
 IncludeDocProperties:=True, _
 IgnorePrintAreas:=False, _
 OpenAfterPublish:=True

Next workSheet

End If

On Error GoTo 0

Exit Sub

Errhandler:

MsgBox "There was an error saving a copy of this document as PDF. " & _
 "Ensure that the PDF is not open for viewing and that the destination path is writable. Error code: " & Err

End Sub

 

Steps:

1. Add Macro: Right Click on the Ribbon Bar > Customize Ribbon > Main Tabs (Click Developer) > Macros > (Project Name=PDFandSaveExcel) > (Macros in PERSONAL.XLSB) > Create
2. Select all and replace with code above.
3. Rename Macro Project as PDFandSaveExcel
4. Customize Quick Access Toolbar > More Commands > Choose Commands from Macros > PERSONAL.XLSB!PDFandSaveExcelSheet (or ExcelWB) > Add > Modify > (Change Icon and Name) > OK

Notes:

• If you get an error 1004, it may be because there is a blank worksheet. Remove it or add junk data and try again - 1 2 3.