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
 

shg

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

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

LeggoMyEggo

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

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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 ..
Code:
.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

New Member
Joined
Feb 15, 2011
Messages
25

ADVERTISEMENT

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

New Member
Joined
Feb 15, 2011
Messages
25
Also, because of the controls, I wouldn't be able to disable the user's ability to select...
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

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

LeggoMyEggo

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

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,817
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,128,129
Messages
5,628,869
Members
416,347
Latest member
AT2021

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