xlNoSelection not persisting after the file is closed

[vikas000000a]

Hello all,

I am creating a report generation program with VB 6.0 as front-end and Oracle 10i as back-end. My reqiurements are
1. User will query the database through the front-end.
2. The results will get populated in an MSHFlexGrid.
3. There will be a button at the bottom captioning "Export to excel" clicking which shoud generate an excel file with the data contained in the grid.
4. User must not be able to copy the data from the generated file. He should be able to see it without any passwords etc. being asked.

So, to solve the above problems, I have made a VB program. I have successfully achieved the first three objectives. For the requirement number four, I am using the following code:

    wksReport.Protect "something_secret"
    wksReport.EnableSelection = xlNoSelection

The above code does protect the sheet and does not allow the user to select any cells. After that I am closing the workbook and here comes the problem into picture.

Actually when I am reopening the file (not programatically, just by double clicking the file), the sheet still is protected but user can very easily select the cells and copy them to a new sheet, thereby defeating the purpose of security and requirement number four.

My doubt is that the EnableSelection property is not persisting when the file is closed and is set to its default value when the file is reopened.

Kindly throw some light on this.

Thanks in anticipation.

[Killer42]

I don't know much about it, but I have one question. When you refer to "closing" the file after setting the protection - are you saving it?

[QVeen72]

Hi,

Not sure of this , but the coding Sequence should be in reverse order (Check Excel Help):

wksReport.EnableSelection = xlNoSelection
wksReport.Protect "something_secret"
 

and as Killer said, dont forget to save the sheet before quitting..

Regards
Veena

[vikas000000a]

I am definitely saving the file before closing it.

Also, I have tried it both the ways, i.e., first protect then setting EnableSelection property and vice-versa. Actually it doesn't matter what you do first beacuse I am anyway saving the file before closing it. So every operation on the workbook should get saved.

Any other expert can show me the way???

[vikas000000a]

Is anybody there to help me out on this?

[Killer42]

Ahah! Pretty sure I found your answer. MS acknowledge the problem, and have a workaround, though not really a solution.

See Some of the properties of worksheets are not preserved in Excel

[vikas000000a]

Ahah! Pretty sure I found your answer. MS acknowledge the problem, and have a workaround, though not really a solution.

See Some of the properties of worksheets are not preserved in Excel

I read the article and got your point. But now, is there any other way to achieve my requirement number four.

I cann't put macros in programatically generated excel files beacuse they are hundreds in number and also many people keep macros disabled for security reasons.

[Killer42]

I read the article and got your point. But now, is there any other way to achieve my requirement number four.

I cann't put macros in programatically generated excel files beacuse they are hundreds in number and also many people keep macros disabled for security reasons.

With MS effectively throwing up their hands in defeat, I don't see how it could be done. I only glanced at the article - there was no patch or anything?

The only other alternatives I can think of are...

• Use a different spreadsheet program instead of Excel.
• Display the data in your own program, rather than using spreadsheet.
• Use your program to drive Excel for display purposes, and do what the macro might not be able to do.

[vikas000000a]

With MS effectively throwing up their hands in defeat, I don't see how it could be done. I only glanced at the article - there was no patch or anything?

The only other alternatives I can think of are...

• Use a different spreadsheet program instead of Excel.
• Display the data in your own program, rather than using spreadsheet.
• Use your program to drive Excel for display purposes, and do what the macro might not be able to do.

All of your alternatives are useless for me as my client requirements are fixed and have been mutually agreed between us months back. It will be something like taking up the challenge and then stepping backward saying that I didn't know that it is not possible using Microsoft Technologies.

[QVeen72]

All of your alternatives are useless for me as my client requirements are fixed and have been mutually agreed between us months back.

Instead of saying "useless",
How about using some good words like "Does not fulfill my requirement"...?
when some one is trying to help you out be generous in action..

[Killer42]

Instead of saying "useless",
How about using some good words like "Does not fulfill my requirement"...?
when some one is trying to help you out be generous in action..

Never mind.

vikas000000a, I just don't see how it can be done within the given restrictions. You are apparently stuck with MS Excel as the tool that must be used. MS say Excel cannot do it. What do you want from us?

Perhaps upgrading Excel would resolve the issue - I don't know.

Hm...

Just how "fixed" are the requirements? Perhaps you could password-protect the worksheet so it can only be opened by your program. Your program could then simply open it using the password you've built in, and set the protection on. Then you let the users go ahead with whatever they want to do. Does that sound feasible?

[vikas000000a]

Instead of saying "useless",
How about using some good words like "Does not fulfill my requirement"...?
when some one is trying to help you out be generous in action..

Sorry QVeen72, I never did intend to offend you. It was a general slip of tongue. Take it easy just like the other person 'who actually is trying to help me' did.

[vikas000000a]

Never mind.

vikas000000a, I just don't see how it can be done within the given restrictions. You are apparently stuck with MS Excel as the tool that must be used. MS say Excel cannot do it. What do you want from us?

Perhaps upgrading Excel would resolve the issue - I don't know.

Hm...

Just how "fixed" are the requirements? Perhaps you could password-protect the worksheet so it can only be opened by your program. Your program could then simply open it using the password you've built in, and set the protection on. Then you let the users go ahead with whatever they want to do. Does that sound feasible?

Hello Killer,
Thanks for your kind of support. But the fact remains that my problem is still unresolved. I will further explore it on other sites.

Meanwhile, I found one hole in the wall. It is that if you make an excel file as usual (I mean, not programmatically), and then protect it with disallowing the the selection of cells, when you close and reopen the file, it does remain persisted as it is (the way I want it to be).

I think, I can somehow or the other make use of this fact. Although, I am not still very sure about it.

Anyway, thanks again.

[Killer42]

Hello Killer,
Thanks for your kind of support. But the fact remains that my problem is still unresolved. I will further explore it on other sites.

Sorry we couldn't help more. If you do find an answer, I'm sure we'd all appreciate your letting us know. That will help anyone else who comes looking for the info in future.

Meanwhile, I found one hole in the wall. It is that if you make an excel file as usual (I mean, not programmatically), and then protect it with disallowing the the selection of cells, when you close and reopen the file, it does remain persisted as it is (the way I want it to be).

Now that is interesting! I wonder how that came about. The automation interface must be failing to set something that the "real" application does.

Maybe there is some way you can take advantage of it. For example, after you turn on the protection, perhaps you could display the spreadsheet and tell Windows that the user pressed Ctrl-S or something. A lot would depend on whether the bug is in the setting of the protection, or the save. The save seems more likely though, since you said that the protection works until the file is reopened.

[vikas000000a]

Sorry we couldn't help more. If you do find an answer, I'm sure we'd all appreciate your letting us know. That will help anyone else who comes looking for the info in future.

Now that is interesting! I wonder how that came about. The automation interface must be failing to set something that the "real" application does.

Maybe there is some way you can take advantage of it. For example, after you turn on the protection, perhaps you could display the spreadsheet and tell Windows that the user pressed Ctrl-S or something. A lot would depend on whether the bug is in the setting of the protection, or the save. The save seems more likely though, since you said that the protection works until the file is reopened.

I would certaily let the forum know when I encounter with the solution.

[vikas000000a]

Sorry we couldn't help more. If you do find an answer, I'm sure we'd all appreciate your letting us know. That will help anyone else who comes looking for the info in future.

Now that is interesting! I wonder how that came about. The automation interface must be failing to set something that the "real" application does.

Maybe there is some way you can take advantage of it. For example, after you turn on the protection, perhaps you could display the spreadsheet and tell Windows that the user pressed Ctrl-S or something. A lot would depend on whether the bug is in the setting of the protection, or the save. The save seems more likely though, since you said that the protection works until the file is reopened.

Hey Killer,
I got the solution of my problem. I installed MS Office 2007 and run the same program without any modification at all. Now the excel files are protected as I wanted them to be. As I have to give only the output excel files to the users, it doesn't bother me if the end user has got Office 2007 or not.

Finally, I did it man.

[sistaskriket]

I had exactly the same problem with Excel 2002.
With Excel 2003 the macrocode works as intended, meaning that NoSelection persists after Close.
When I open the worksheet manually the protected cells can not be selected. That goes for the entire worksheet.
In addition to that I also hide the columns where I have my protected cells..