Export a PDF for others to view and me to edit/update

[LeggoMyEggo]

Hello all,

I am working on exporting a pdf so that it can be accessed easily from a large number of computers. However, when new data is calculated twice a day I would like to save over this pdf with a new one. Excel gives me an error if I try and export the pdf and someone has the file open (which is bound to happen if I send out the file location)...

How can I distribute a pdf file and still be able to replace it?

I am fairly flexible and I'm just looking for a solution so if this would work better with .xps or something else, I am open to a solution.

I tried making the file read-only but that doesn't appear to work. Will it work and I am just not doing it properly?

My code to export the file is:

ConstantFileName = "string for file location"
Application.PrintCommunication = True
    ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ConstantFileName _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False

Thanks,
Alex

 

[shg]

Perhaps save the file as read-only:

    Const sFile     As String = "full path and file name"
    
    Application.PrintCommunication = True
    SetAttr sFile, vbNormal
    ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, _
                                       Filename:=sFile, _
                                       Quality:=xlQualityStandard, _
                                       IncludeDocProperties:=True, _
                                       IgnorePrintAreas:=False, _
                                       OpenAfterPublish:=False
    SetAttr sFile, vbReadOnly

 

[LeggoMyEggo]

Thank you for the quick reply, but I already tried setting it as read only... I used this code:

Sub ReadOnlyStatus(Enable As Boolean, ConstantFileName As String)
'This function changes the ConstantFileName to read only if enable = True, and to non-read only if enable=false
Const READ_ONLY = 1
Const NOT_READ_ONLY = 0
Dim FileSys As Object 'FileSystemObject
Dim ObjFile As Object
Set FileSys = CreateObject("Scripting.FileSystemObject")
Set ObjFile = FileSys.GetFile(ConstantFileName)
'Set ObjFile = FileSys
If Enable = True Then
    If ObjFile.Attributes And READ_ONLY Then
   ' do Nothing
    Else
      ObjFile.Attributes = ObjFile.Attributes Xor READ_ONLY
    End If
Else
    If ObjFile.Attributes And READ_ONLY Then
       ObjFile.Attributes = ObjFile.Attributes And NOT_READ_ONLY
    Else
        'do nothing
    End If
End If
End Sub

Does yours do something differently?

Also, if I set the file as read only how will I re-output over it?

 

[shg]

... if I set the file as read only how will I re-output over it?

    Const sFile     As String = "full path and file name"
 
    Application.PrintCommunication = True
[COLOR=red]   SetAttr sFile, vbNormal[/COLOR]
    ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, _
                                       Filename:=sFile, _
                                       Quality:=xlQualityStandard, _
                                       IncludeDocProperties:=True, _
                                       IgnorePrintAreas:=False, _
                                       OpenAfterPublish:=False
[COLOR=red]   SetAttr sFile, vbReadOnly[/COLOR]

 

[LeggoMyEggo]

Just tried your code with the file open on another machine, and it gives the error:
Run-Time error "1004"
Document not saved. The file may be open, or an error may have been encountered while saving.

on the ActiveWorkbook.ExportAsFixedFormat line
Exact code here:

Sub TestFileOutput()
ConstantFileName = "S:\Services Cincinnati\Central Services Scorecards\Official Scorecard.pdf"
SetAttr ConstantFileName, vbNormal
Application.PrintCommunication = True
    ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ConstantFileName _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False
 
SetAttr ConstantFileName, vbReadOnly
 
End Sub

Runs fine without the file open on another computer....


Thanks,
Alex

 

[shg]

You have to start with the file read-only, so that people can't lock it by opening it. Otherwise you can't change the attributes.

And I think better pseudo-logic would be this:

Write to a local temporary file
If sFile exists then 
   Change attributes to Normal 
   If error in changing attributes then abort with message
Name temp file As sFile
Change sFile to readonly

 

[shg]

Maybe like this (untested):

Sub x()
    Const sTemp     As String = "local path and file name"
    Const sFile     As String = "ultimate path and file name"
 
    If Len(Dir(sTemp)) Then Kill sTemp
 
    Application.PrintCommunication = True
    ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, _
                                       Filename:=sTemp, _
                                       Quality:=xlQualityStandard, _
                                       IncludeDocProperties:=True, _
                                       IgnorePrintAreas:=False, _
                                       OpenAfterPublish:=False

    On Error Resume Next
 
    If Len(Dir(sFile)) Then
        SetAttr sFile, vbNormal
        If Err.Number Then
            MsgBox "Unable to change attributes of " & sFile
            Err.Clear
            Exit Sub
        End If
    
        Kill sFile
        If Err.Number Then
            MsgBox "Unable to delete " & sFile
            Err.Clear
            Exit Sub
        End If
    End If
 
    Name sTemp As sFile
    If Err.Number Then
        MsgBox "Unable to name " & sTemp & " as " & sFile
        Err.Clear
        Exit Sub
    End If
 
    SetAttr sFile, vbReadOnly
End Sub

 

[LeggoMyEggo]

Unfortunately, I just tried your code and it does not seem to work.
The only line I omitted was the initial "check if the temporary file name exists and delete it" because my temporary file name is based on the date and time and will be unique.

I tested by making the permanent file location read-only and asking a co-worker to open it on their machine.
I then ran the code and was unable to delete the file.
(the messagebox stating that the file could not be deleted was activated).

How else could I try this...?

Thank you,
Alex

Here is my code for reference, in case I made a mistake implementing yours:

ConstantFileName = "S:\Services Cincinnati\Central Services Scorecards\Official Scorecard.pdf"
VariableFileName = "S:\Services Cincinnati\Central Services Scorecards\Scorecard Archive\Official Scorecard " & Format(Now, "mm-dd-yyyy hh nn") & ".pdf"

 
Application.PrintCommunication = True
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=VariableFileName _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False
On Error Resume Next
If Len(Dir(ConstantFileName)) Then
SetAttr ConstantFileName, vbNormal
If Err.Number Then
MsgBox "Unable to change attributes of " & ConstantFileName
Err.Clear
Exit Sub
End If
 
Kill ConstantFileName
If Err.Number Then
MsgBox "Unable to delete " & ConstantFileName
Err.Clear
Exit Sub
End If
End If
Name VariableFileName As ConstantFileName
If Err.Number Then
MsgBox "Unable to name " & VariableFileName & " as " & ConstantFileName
Err.Clear
Exit Sub
End If
SetAttr ConstantFileName, vbReadOnly

 

[shg]

Hmm.

Did you you verify that the file was indeed read/write after the code ran?

 

[LeggoMyEggo]

I just checked and the code worked as intended, it changed the pdf to where it was no longer "read only". But it then encountered excel error 70 (permission denied) when attempting to kill the file.
On the:
Kill ConstantFileName
line...

(the file had been opened on another computer at the time when it was a read-only file)

Any other ideas? If you can think of a better way to distribute it (email is not really a possibility) I would be open to that as well!

Thank you for your continued help,
Alex