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
 

LeggoMyEggo

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

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

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,814
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows
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:
Rich (BB 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
 
Last edited:

LeggoMyEggo

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

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,814
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows
Alex, kudos to IT guys and thank you for letting us know that you’ve worked it out! :)
Vlad
 

Watch MrExcel Video

Forum statistics

Threads
1,127,107
Messages
5,622,782
Members
415,927
Latest member
vedasinternational

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