Excel macro (VBA): Export to ReadOnly PDF

[ChrFriberg]

Dear everyone!
I'm completely new to this site - so forgive me in advance..

I'm no genius - especially not when it comes to VBA, but I thought that you guys are, so:

I have a macro in my excel worksheet that does following:
Saves the active sheet as a PDF

The code looks like:

Filename:="C:\Users\Christian\Dropbox\Myhomepage.dk\Faktura\AutoFaktura\" & _        
ActiveSheet.Range("'Faktura (Privat salg)'!D12").Value & ".pdf", _
OpenAfterPublish:=False

I want the PDF to be saved as "ReadOnly" - so I don't get to overwrite it.


Can you help me?

A nice weekend to everyone!

/Christian

 

[ParamRay]

.
.

Try something like this:

Sub Call_Sub()

    Call ExportAsReadOnlyPDF(ActiveSheet)

End Sub

'------------------------------------------------------------

Private Sub ExportAsReadOnlyPDF(wksToExport As Worksheet)

    'exports a worksheet as pdf
    'with read-only attributes

    Dim bSuccess As Boolean
    Dim sExportFullName As String
    Dim sPrompt As String
    Dim iButtons As Integer
    
    bSuccess = False
    
    'construct path and filename
    If Len(wksToExport.Parent.Path) = 0 Then
        sExportFullName = Application.DefaultFilePath & _
            Application.PathSeparator & _
            wksToExport.Parent.Name & ".pdf"
    Else
        sExportFullName = wksToExport.Parent.Path & _
            Application.PathSeparator & _
            Split(wksToExport.Parent.Name, ".")(0) & ".pdf"
    End If
    
    On Error GoTo Notification
    
    'export the workbook
    'change parameters as needed
    wksToExport.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=sExportFullName, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    
    On Error GoTo 0
    
    bSuccess = True
    
    'set the attributes to read-only
    SetAttr _
        PathName:=sExportFullName, _
        Attributes:=vbReadOnly
    
Notification:
    If bSuccess Then
        sPrompt = "Read-only export successful:" & _
            vbCrLf & sExportFullName
        iButtons = vbInformation
    Else
        sPrompt = "Read-only export failed."
        iButtons = vbExclamation
    End If

    MsgBox Prompt:=sPrompt, Buttons:=iButtons

End Sub

 

[ChrFriberg]

Thanks a lot, gpeacock!