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]

Hmm.

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

I think that answers this question, but to be certain: Was the file read-write or read-only when the user opened it?

Does the code work if you do it yourself in a controlled environment, with a fle that no one else knows about?

Create the file on a local drive, make it read only, then you open it and leave it open, and then run the code to replace it. Does that work?

Do the same thing on a network share -- does that work?

Then the same thing with someone else opening it (call them: "Open it now, Bob.")

 

[LeggoMyEggo]

I have been running this code on a pdf file that only I use, so that aspect has been controlled from the beginning.

I ran your code without the file open, and it worked.
I ran it again, after opening the file on another computer. I checked the properties of the file before opening it, and the "read-only" checkbox was indeed checked. The code failed as I described before.

I don't think I need to do more testing, but do I...?

Thanks,
Alex

 

[shg]

Hmm.

My code works fine for me when the file is a workbook, but not a PDF. Apparently Adobe is locking the file despite it being read only, and although I can change the attributes, as you can, I can't delete it either when it's open.

I'm not having any good ideas springing to mind.

 

[Peter_SSs]

My code works fine for me when the file is a workbook, but not a PDF.

I'm not having any good ideas springing to mind.

This is not an area I have any expertise in and perhaps I haven't fully understood the conversation so far, but just thinking about the logic ..

1. It doesn't seem logical that you could kill any file if somebody has it open. Can you do that?

2. However, if you can do what is required with an Excel file, then why not give everybody access to an Excel file? It could be protected by password and the sheet(s) further restricted with ..

.EnableSelection = xlNoSelection

.. meaning users who open it could pretty much just look at it like a pdf (unless you have mischievous users of course ).

 

[LeggoMyEggo]

Well I have thought about distributing the workbook itself, and one sheet is already password protected, as is the code.
The workbook has a number of active-x controls to jump to different portions of the workbook, so I was just trying to simplify looking at the final results for the users.
I also have to look into it further to see if the computers I'm outputting to (on the shop floor of a machine shop) have permission to view excel files.

Essentially, I was picking pdf for simplicity... Is there a way to make it work?


Thanks,
Alex

 

[LeggoMyEggo]

Also, because of the controls, I wouldn't be able to disable the user's ability to select...

 

[Peter_SSs]

Also, because of the controls, I wouldn't be able to disable the user's ability to select...

Why not? They cannot select any cells/controls in the pdf so they shouldn't need any controls in the Excel file. So couldn't you hide/delete any controls before 'Save As - VariableFileName' (which would now be an Excel file name)?

 

[ZVI]

Peter has offered very reasonable decision.
Replace formulas by values, save XLSM as XLSX to remove all VBA code, protect workbook and sheets with required options. After that set read only file attribute or even better place file on shared resource with full access for you, but with read only permission for others.

Another way - using VBA put PDF into ZIP archive and share it.

 

[LeggoMyEggo]

To all:

It has come to my attention that the machines I will be distributing the information to do not have access to Excel.
So I need an option that does not involve using the excel workbook itself.
Is PDF the only way? If so, how do I make it so I can continue to edit it/rewrite over it...?


ZVI: Could you please elaborate on your .zip archive plan? What are the benefits of using a .zip?


Thanks,
Alex

 

[ZVI]

ZVI: Could you please elaborate on your .zip archive plan? What are the benefits of using a .zip?

If one load PDF from the shared ZIP archive then PDF file is temporary copied out from archive to the local temp folder from which it is loaded by PDF-associated software like Adobe Acrobat Professional or the similar. At this time another person could delete/update PDF file in the shared ZIP archive. After quitting of PDF-associated software the temporary local folder and the copy of PDF file are deleted from the user’s PC drive.

In other words, in this case each user deals with the local copy of PDF file rather than with single shared file, therefore the locking issue does not happen