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]

Ignore the above. It has a mistake.

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:=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

 

[jolivanes]

Please be polite and let possible helpers know that you have placed the same question on other sites.
You don't want people to use their volunteer time to work on your problem if there might have been a solution posted on one of the other sites.
You should have hyperlinks posted on ALL other sites where you posted regarding this problem.
All sites have pretty well the same requirement regarding cross posting.

 

[drefiek2]

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

Hi, thanks I did try it but it just overwrites regardless of the file already existing. Thanks for your help on this matter though very much appreciated, it has got me off the starting line!

 

[rlv01]

One more possibility, but no gaurantees with SharePoint.

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

    If Dir(SharePointPath & PdfFileName) <> "" Then
        MsgBox "File already exists"
    Else
        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
    Exit Sub

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

 

[drefiek2]

One more possibility, but no gaurantees with SharePoint.

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

    If Dir(SharePointPath & PdfFileName) <> "" Then
        MsgBox "File already exists"
    Else
        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
    Exit Sub

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

Hi,
Tried and was given error '52 - bad file name or number'.

 

[rlv01]

Tried and was given error '52 - bad file name or number'.

Then that method likely does not work with an HTTP style of path either.

 

[drefiek2]

Then that method likely does not work with an HTTP style of path either.

Just a thought but would the IF function need to check whether the http URL of the SharePoint file exists instead of using Dir:

  If Dir(SharePointPath & PdfFileName) <> "" Then
        MsgBox "File already exists"

 

[rlv01]

Just a thought but would the IF function need to check whether the http URL of the SharePoint file exists instead of using Dir:

No way to know other than experimentation. I cannot experiment on your company's SharePoint setup, but you can. The other way to solve this would be to map your SharePoint folder to a standard drive letter or UNC path. Then the standard ways to check for a file already existing would work.

 

[drefiek2]

No way to know other than experimentation. I cannot experiment on your company's SharePoint setup, but you can. The other way to solve this would be to map your SharePoint folder to a standard drive letter or UNC path. Then the standard ways to check for a file already existing would work.

Hi Rlv01 and everyone again, I hope you can help with an extra question I have.
Regarding the script I have used (I've pasted it again below), I want it so that when you click the button it will come up with a message box first which asks "Have you checked the date?", with an OK ("Yes") (which then executes the script as expected) and a Cancel ("No"), which simply does not execute the script.

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 = "COMPANYLINK" '<<<<<<<<<<<< edit as required.

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

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

    msg = "Handover successfully uploaded to SharePoint."
    msgbox msg, vbInformation, "Upload Successful"
    Exit Sub

SaveError:
    msg = "Handover was not uploaded to SharePoint. Please contact X on e-mail, manually Save as PDF and upload to the SharePoint folder." & vbCr & vbCr & Err.Number & " - " & Err.Description
    msgbox msg, vbCritical, "Upload Failure"
End Sub

 

[rlv01]

One way:

    Select Case MsgBox("Have you checked the date?", vbYesNo Or vbQuestion, Application.Name)
    Case vbNo
        Debug.Print "User exit"
        Exit Sub
    End Select

Place the code near the start of your macro.

This new question does not have much to do with the original topic of this thread. A thread on Mr Excel has two main purposes. The first is to help the person who created the thread and posted the question (in this case, you). The second is to help other members of the forum that might have a similar problem and are searching for posts that could help them. Changing the topic and asking questions on a different subjects defeats the second purpose, so the better course of action is to create a new thread for new and unrelated questions.