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

 

[LeggoMyEggo]

Ok, the .zip procedure does work when I tried it manually. What is the code and the syntax for converting a pdf to a .zip in VBA...?


Also, as an alternative, I was thinking of simply placing a shortcut to a folder on the computers that need to view the file, and just releasing a new file with a timestamp periodically.
However, I'd like to be able to delete all files that aren't currently open (which might just be the ones that don't produce an error) so that I can clean up the old files when people stop using them.
What would the syntax for that be...? I can delete them all with something like:

Kill "C:\Documents\*.pdf"

But what happens if there is an error? Can I delete individually even if I don't know the names?


Thank you all,
Alex

 

[ZVI]

Ok, the .zip procedure does work when I tried it manually. What is the code and the syntax for converting a pdf to a .zip in VBA...?

Here is the code:

Sub WbToPdfToZip()
  
  ' --> Settings, change to suit
  Const PdfName$ = "Test.pdf"
  Const ZipFullName$ = "C:\SharedFiles\Test.zip"
  ' <-- End of settings
  
  Dim PdfTempName$
  PdfTempName = Environ("Temp") & "\" & PdfName
  If Len(Dir(PdfTempName)) Then Kill PdfTempName
  ActiveWorkbook.ExportAsFixedFormat xlTypePDF, PdfTempName, xlQualityStandard, True, False, OpenAfterPublish:=False
  Zip ZipFullName, PdfTempName
  If Len(Dir(PdfTempName)) Then Kill PdfTempName

End Sub

' ZVI: Copy Files to ZipFullName archive
Sub Zip(ZipFullName, ParamArray Files())
  Dim FileNumber%, ZipFile$, i&, x
  ' Add ZIP extentions if not set
  ZipFile = Trim(ZipFullName)
  If LCase(Right(ZipFile, 4)) <> ".zip" Then ZipFile = ZipFile & ".zip"
  ' Create an empty zip archive if not already present
  If Len(Dir(ZipFile)) = 0 Then
    FileNumber = FreeFile
    Open ZipFile For Output As #FileNumber
    Print #FileNumber, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0)
    Close #FileNumber
  End If
  ' Copy files to archive
  With CreateObject("Shell.Application").Namespace((ZipFile))
    For Each x In Files
      .CopyHere (x)
    Next
    Do Until .Items.Count = UBound(Files) + 1
      DoEvents
    Loop
  End With
End Sub

 

[LeggoMyEggo]

Hello All,

Just thought I'd let you guys know how the problem was eventually solved...

My local IT guy knows VBScript, and he wrote a small program in that (which he converted to a .exe) that forces a user to make a copy of the pdf in their temporary folder when executed.
That way, the original is only being accessed for maybe a second at a time, and after that second it's free to be accessed by the next person.
So the original pdf is never manually opened, only this .exe file is opened which makes an individual copy for the user. It will also check to see if there is a newer version of the pdf each time it is executed so it will bring up the latest version each time.

All my excel does is update that one pdf file!

Thanks for all your help though!

Alex

 

[ZVI]

Alex, kudos to IT guys and thank you for letting us know that you’ve worked it out!
Vlad