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

LeggoMyEggo

New Member
Joined
Feb 15, 2011
Messages
25
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:
Code:
ConstantFileName = "string for file location"
Application.PrintCommunication = True
    ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ConstantFileName _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False

Thanks,
Alex
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,783
Office Version
  1. 2010
Platform
  1. Windows
Perhaps save the file as read-only:

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

New Member
Joined
Feb 15, 2011
Messages
25
Thank you for the quick reply, but I already tried setting it as read only... I used this code:

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

MrExcel MVP
Joined
May 7, 2008
Messages
21,783
Office Version
  1. 2010
Platform
  1. Windows
... if I set the file as read only how will I re-output over it?
Code:
    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

New Member
Joined
Feb 15, 2011
Messages
25

ADVERTISEMENT

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:

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

MrExcel MVP
Joined
May 7, 2008
Messages
21,783
Office Version
  1. 2010
Platform
  1. Windows
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:

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

MrExcel MVP
Joined
May 7, 2008
Messages
21,783
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Maybe like this (untested):

Code:
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
 
Last edited:

LeggoMyEggo

New Member
Joined
Feb 15, 2011
Messages
25
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:
Code:
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
 
Last edited:

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,783
Office Version
  1. 2010
Platform
  1. Windows
Hmm.

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

LeggoMyEggo

New Member
Joined
Feb 15, 2011
Messages
25
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,861
Messages
5,627,308
Members
416,239
Latest member
Counselor85027

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top