How to assign a specific button in excel sheet to do some specific task?

Question

I would like to have a button on this excel sheet whose name should be “Save as PDF”.

When I press this button it should save the excel sheet’s all data into PDF at the path- M:\formats\ ‘File Name’

‘File Name’ should be the value of cell No H8.

In another words when I click “Save as PDF” button it should save the excel file in pdf form, into the above mentioned path and also with the name whichever is written in cell no H8.

For example, if the name ANDREW PITTERSON is written in H8 cell then it should save with the same name I.e. ANDREW PITTERSON.

Kindly look at this snapshot.

https://i.sstatic.net/LQxsT.jpg THANKS

Answer 1

Here's a link to a great simple article, to do this sort of thing. http://www.contextures.com/excelvbapdf.html

I've tested the code example in Excel 2013 and it works fine. The code asks the user what directory to save the PDF in.

But your question says that you also want to save to a specific location (without user intervention) and to get the filename from a cell.

Update - And you'd also like to save the file as an XLSM, once the PDF is created.

The code below does what you're after (all credit to the original author, but my own OCD led me change var names to a format that I like).

I'm guessing the OP would like to know HOW it was done, rather than just have the answer, so I've tried to make the example easy to follow, rather than trying to observe best practice - I'd appreciate not being down-voted for this.

Please note, you must first open the Excel Code window, got to Tools, then References and select 'Microsoft Scripting Runtime' then click Ok. This lets you use many useful functions.

I keep the directory path (hard coded) and the filename separate, so that I can get the 'BaseName' in a clearer way. Obviously this could be done in less lines, but at the risk of making it harder to follow.

Sub ExportAPDF_and_SaveAsXLSM()

    Dim wsThisWorkSheet As Worksheet
    Dim objFileSystemObject As New Scripting.FileSystemObject

    Dim strFileName As String
    Dim strBasePath As String

    strBasePath = "M:\formats\"
    strFileName = Range("H8")

    On Error GoTo errHandler

    Set wsThisWorkSheet = ActiveSheet

    wsThisWorkSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=strBasePath & strFileName, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False

    MsgBox "PDF file has been created."

    ' Now we need to get rid of the .PDF extension.  Many ways to code round this, but here is a built in function.
    strFileName = objFileSystemObject.GetBaseName(strFileName) & ".xlsm"

    wsThisWorkSheet.SaveAs Filename:=strBasePath & strFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled

    MsgBox "Workbook now saved in XLSM format."


exitHandler:
        Exit Sub
errHandler:
        MsgBox "Could not create PDF file"
        Resume exitHandler

End Sub

Just add the button (ActiveX Button) to your worksheet and call this sub from the buttons code window (or just paste the code directly into the Button code window).

Hope that helps.

ADDED: To save the file as an XLSX (No Macros), then replace the code toward the end of the SUB with:

Application.DisplayAlerts = False
strFileName = objFileSystemObject.GetBaseName(strFileName) & ".xlsx"
wsThisWorkSheet.SaveAs Filename:=strBasePath & strFileName, FileFormat:=xlOpenXMLWorkbook
Application.DisplayAlerts = False