View Only excel?

[davidbeach]

Is there a way of protecting an excel (2010) spreadsheet so that it becomes view only to anybody else? A quick google search doesn't turn up anything. I have data to share, but it becomes stale very quickly and others in the organization have a tendency to print something out once and refer to it for the next 10 years. I want to publish it on SharePoint and force everybody back to the SharePoint source anytime they want to reference the data.

 

[IDS]

Not sure what you googled, but mine came up with:

http://www.k2e.com/tech-update/tips/728-tips-adding-a-password-to-make-an-excel-workbook-read-only

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[GregLocock]

Does that lock down 'save as' and 'print'?

Personally I think the cause is hopeless, since a sufficiently determined user will just print screenshots. perhaps you could watermark the spreadsheet with "Latest data for this sheet is at

http://www.idontknow.com.

This data is current as of xyz"

Incidentally the concept of transient records in a serious information retention system gets around this, my personal paper copies of a spreadsheet would be destroyed after 3 years, maximum, more likely two years (I quite simply empty the 2014 drawer into the shredder).


Cheers

Greg Locock


New here? Try reading these, they might help FAQ731-376

http://eng-tips.com/market.cfm?

 

[IDS]

Greg - I guess the best you can do is save the spreadsheet as a pdf, then save that to not allow printing or save-as.

But even then, pdf passwords are easily circumvented, and as you say, there is nothing to stop people doing a screenshot.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[davidbeach]

The whole thing would take 2180 rows (so far, more to come) by 126 columns (also probably more to come) so that I think screen shotting it would be far more difficult than going back to SharePoint every time, but if somebody wants to go to that length, they can knock them self out. The k2e.com link appears to be about read only, not view only; lots of information available about read only. Using SharePoint as the distribution medium I'm not worried about my data getting corrupted; and as long as the code that reads a text file from another application isn't corrupted the data in the spreadsheet is a dead end anyway; the next version of the spreadsheet will come from fresh external data. The SharePoint file may not even include the VBA code.

I'd like to leave it as excel so that people can grab small bits of data for immediate use; that's what it's for but I don't want anybody using this month's data 3, 5, or 10 years from now. I'm working on an extract of the relay settings for our entire system so that other groups that may need to know what the settings are today can access them. But settings change, often, and I want (as does my management) everybody to go back to the well every time they want any setting information. Saving a copy is far too easy; making screen shots of a huge pile of data is, in my estimation, too much work.

If view only isn't possible, I'm probably looking at a .pdf solution with watermarks across the whole thing that the data is void after xx/xx/xxxx. But that's more work for me and somewhat less useful for others. I'd love to just trust people to go back for fresh data every time, but there's well established precedent that they won't. And since they won't, we'll get the "but you said..." complaints when reality doesn't match their year's old spreadsheet.

 

[SkipVought]

If this is a serious issue you need to get management involved to change the culture. If management does not care, then why should you?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Denial]

Nothing's fool proof, particularly when the fool is determined.[ ] I'd try using VBA as an extra layer of protection to anything else you do.[ ] Put the data on a "very hidden" sheet.[ ] Have the workbook_open event handler make the sheet visible, but only if the system date satisfies some sort of recency test.[ ] Obviously you also need to use the workbook_save even handler to make your sheet invisible again.

 

[drawoh]

davidbeach,

How about a PDF with a watermark that includes the date, and dire warnings about the document going out of date?

--
JHG

 

[Agent666]

Just like you can create additional ribbon tabs and buttons, you can also use vba to hide tabs in the ribbon on excel 2007 & above. Might at least give the appearance of removing functionalities in combination with other measures.

I like Denial's suggestion.
You can also use vba to alter the visibility of a piece of word art which is used as a watermark. Use this in combination with some code for how current the data is.

Capturing a before print event can enable you to hide the data so only a blank page is printed. A before save event can be used to delete all of the data. Set print area to a blank cell, remove ribbon, remove ability to right click cells, etc... Basically cripple excel when in the spreadsheet.

Some form of punishment (naming and shaming) should follow for those who consistently circumvent the way the data should be used, as skip said management needs to support the correct way of using the data... If not you'll never get there.

 

[SkipVought]

As Denial suggested, make the sheet in question VeryHidden (can only be done via VBA or the VB Editor). Add a slash sheet that tells the user to Enable Macros. When the user enables macros, the VeryHidden sheet become visible and the splash sheet is hidden.

The Before_Print event will Cancel any Excel print command.

The Before_Save event will cancel any Save/SaveAs command.

Opening workbook with your UID will bypass these event results.

This will not prevent someone doing a screen print.

I would further suggest that YOUR "official" print copy, that comes from you as paper or PDF, contain a header/footer that explicitly states, "Valid until date."

Of course all this needs VBA to "lock down" and require the user to Enable Macros or else they don't get a look, if the users will have access to the workbook.

If you want help doing this, you'll get better help at our sister site:

http://www.tek-tips.com/threadminder.cfm?pid=707

You may want the users to grab data from this sheet for their use. Hmmmmmmm. Does it matter that when you publish the next version, they still have old data that they copied/pasted from the former version? One other possibility is to give users a user defined spreadsheet function that returns certain data based on some argument value(s). Not knowing the nature of your data it would be impossible to venture a guess at how that might be accomplished. Over a period of 20 years I've supplied a user community with dozens of functions that returned near current data from staged production data tables including part requirements, planned orders, production orders, inventory level, production order operation and much more.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[davidbeach]

Thanks for the suggestions. At this point I think I'm leaning toward posting a watermarked PDF. The VBA stuff looks interesting but seems to be getting into the realm of playing games that I don't want to play.

 

[SkipVought]

I would agree!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[IFRs]

Google Excel to EXE