Excel Macro for Save as PDF (to a share point folder)

[drefiek2]

Hi,
I am very new to macros and would like to know how I can do the following. I have a single sheet in an excel workbook and I want a button which will:
1.save the sheet (an array of cells) as a PDF (landscape) in a particular work sharepoint folder
2. The pdf file name should be whatever is in cell C3
3. If the save is successful I want a pop up message box to say that. If it is unsuccessful I would like an error message box to say that.


Thank you in advance.

 

[rlv01]

Use the Excel Macro Recorder to record yourself performing Step 1 as you have described above. Then post the code generated by the macro recorder here and we will see if it is possible to automate it to save to your SharePoint folder.

 

[rlv01]

Don't forget to use code tags when posting your code.

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

 

[drefiek2]

Hi, I recorded the macro - here it is:
Note I have removed company information from the web address.

Sub SaveAsPDF()
'
' SaveAsPDF Macro
'
' Keyboard Shortcut: Ctrl+Shift+S
'
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
        "https://COMPANY-my.sharepoint.com/personal/COMPANY/Test.pdf" _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False
End Sub

 

[rlv01]

So, one way to do it.

Sub SaveAsPDF()
'
' SaveAsPDF Macro
'
' Keyboard Shortcut: Ctrl+Shift+S
'
    Dim SharePointPath As String
    Dim PdfFileName As String
    Dim Msg As String

    On Error GoTo SaveError

    SharePointPath = "https://COMPANY-my.sharepoint.com/personal/COMPANY/" '<<<<<<<<<<<< edit as required.

    PdfFileName = ActiveSheet.Range("C3").Value

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

    Msg = "File save was successful"
    MsgBox Msg, vbInformation, "Success"
    Exit Sub

SaveError:
    Msg = "File save was not successful:" & vbCr & vbCr & Err.Number & " - " & Err.Description
    MsgBox Msg, vbCritical, "Save Error"
End Sub

 

[drefiek2]

Hi, thank you that works perfectly so far! Tried and tested.

Cell C3 is a date cell (U.K format e.g. Monday 24 April 2023). I have used

=TODAY()

as the function here which auto-populates on open.

In the SharePoint folder I have chosen to save the PDFs in, there will be folders for years and then folders for months inside those folders. Is there a way for the macro to use the date in C3 to determine which year and folder to save the PDFs in or is that way out of Excel's reach?

 

[drefiek2]

Hi,
After more testing I noticed that certain date formats in cell C3 will not save as PDF to the specified folder. It doesn't seem to accept / or . within the date e.g. 24/04/2023 or 24.04.2023. I have formatted the date so that it appears as e.g. Monday 24 April 2023 on the sheet, but in the formula bar it still shows 24.04.2023 or 24/04/2023.

 

[rlv01]

Hi,
After more testing I noticed that certain date formats in cell C3 will not save as PDF to the specified folder. It doesn't seem to accept / or . within the date e.g. 24/04/2023 or 24.04.2023. I have formatted the date so that it appears as e.g. Monday 24 April 2023 on the sheet, but in the formula bar it still shows 24.04.2023 or 24/04/2023.

There are certain characters that are illegal to use in a windows file name. Typically:

< (less than)​

> (greater than)​

: (colon - sometimes works, but is actually NTFS Alternate Data Streams)​

" (double quote)​

/ (forward slash)​

\ (backslash)​

| (vertical bar or pipe)​

? (question mark)​

* (asterisk)​

and all Non-printable characters​

Naming Files, Paths, and Namespaces - Win32 apps

All file systems supported by Windows use the concept of files and directories to access data stored on a disk or device.

learn.microsoft.com

 

[drefiek2]

There are certain characters that are illegal to use in a windows file name. Typically:

< (less than)​

> (greater than)​

: (colon - sometimes works, but is actually NTFS Alternate Data Streams)​

" (double quote)​

/ (forward slash)​

\ (backslash)​

| (vertical bar or pipe)​

? (question mark)​

* (asterisk)​

and all Non-printable characters​

Naming Files, Paths, and Namespaces - Win32 apps

All file systems supported by Windows use the concept of files and directories to access data stored on a disk or device.

learn.microsoft.com

Hi, thank you for clarifying. After some searching I've tweaked my code and it now saves as a PDF using the date cell and removing the illegal characters. It works a treat but I have one more thing I would like it to do and I'm not sure how to do it. When saving I do not want it to overwrite if it has already been saved as a PDF (for example the user has forgotten to change the date in C3). How do I add a check to make sure it will not overwrite a saved PDF, including a pop up warning box beforehand? This is my code:

Sub SaveAsPDF()
'
' SaveAsPDF Macro
'
' Keyboard Shortcut: Ctrl+Shift+S
'
    Dim SharePointPath As String
    Dim PdfFileName As String
    Dim Msg As String

    On Error GoTo SaveError

    SharePointPath = "FILE LOCATION" '<<<<<<<<<<<< edit as required.

    PdfFileName = Replace(Range("D6").Value, "/", "") & ActiveSheet.Range("D7").Value

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

    Msg = "Handover successfully delivered to SharePoint."
    MsgBox Msg, vbInformation, "Save Successful"
    Exit Sub

SaveError:
    Msg = "Handover was not delivered to SharePoint:" & vbCr & vbCr & Err.Number & " - " & Err.Description
    MsgBox Msg, vbCritical, "Save Failure"
End Sub

 

[rlv01]

Possibly this, but I have never personally tested whether the FileSystemObject's FileExists method works with an http style SharePoint address.

Sub SaveAsPDF()
    '
    ' SaveAsPDF Macro
    '
    ' Keyboard Shortcut: Ctrl+Shift+S
    '
    Dim SharePointPath As String
    Dim PdfFileName As String
    Dim Msg As String
    
    On Error GoTo SaveError
    
    SharePointPath = "FILE LOCATION" '<<<<<<<<<<<< edit as required.
    
    PdfFileName = Replace(Range("D6").Value, "/", "") & ActiveSheet.Range("D7").Value
    
    With CreateObject("Scripting.FileSystemObject")
        If .FileExists(SharePointPath & PdfFileName) Then
            MsgBox "File already exists"
        Else
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=FPath
            Debug.Print "File Saved"
    
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=SharePointPath & PdfFileName, _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
            
            Msg = "Handover successfully delivered to SharePoint."
            MsgBox Msg, vbInformation, "Save Successful"
        End If
    End With
    Exit Sub
    
SaveError:
    Msg = "Handover was not delivered to SharePoint:" & vbCr & vbCr & Err.Number & " - " & Err.Description
    MsgBox Msg, vbCritical, "Save Failure"
End Sub