No Prompt for Read Only

[SSweez]

I am trying to do something that should be fairly simple but I am not
sure how to do it. I have an excel worksheet that multiple users open
as "read only" and use to view an munipulate data from an Access
database. Users currently open the file and choose "read only" at the
file open prompt. Is there a way to have users click the file and
have it open as "read only" without having to choose "read only" from
a prompt? The current process confuses users because I have a
password prompt inside Excel for users to enter a password to access
selected information from Access. Users try to enter their password
at the time the file opens and not later when they click a buttom
placed on a worksheet. Their password does not work, as intended, at
the startup as a result. Is there a way to get them to have read
acess but not get the password prompt? Or maybe there is another way
to get the same result. Any help is appreciated. Thanks.

 

[Bill Renaud]

<<Is there a way to have users click the file and have it open as "read
only" without having to choose read only" from a prompt?>>

Use the GetOpenFilename and the Workbooks.Open methods like the following:

Const strFileFilter As String = "Excel Files (*.XLS),*.XLS"
Dim strFileName As Variant

strFileName = Application.GetOpenFilename(FileFilter:=strFileFilter, _
Title:="Open Single File
(Read-Only)")

'Open the file in read-only mode (and add to the MRU file list).
Workbooks.Open FileName:=strFileName, ReadOnly:=True, AddToMru:=True

 

[Bill Renaud]

<<Is there a way to have users click the file and have it open as "read
only" without having to choose read only" from a prompt?>>

Correction: Use the GetOpenFilename and the Workbooks.Open methods like the
following. Be sure to check the return type of varFileName, as it will be
FALSE if the user canceled out of the Open dialog box. (I renamed the
variable strFileName to varFileName, as it does need to be a Variant. I
also changed the word wrap a little bit to reduce word wrap in the
newsgroup.)

You may also need to mark the workbook as "Saved" before closing it, to
avoid a "Save the file?" prompt.

Const strFileFilter As String = "Excel Files (*.XLS),*.XLS"
Dim varFileName As Variant

varFileName = Application _
.GetOpenFilename(FileFilter:=strFileFilter, _
Title:="Open Single File (Read-Only)")

If VarType(varFileName) = vbString _
Then
'Open the file in read-only mode (and add to the MRU file list).
Workbooks.Open Filename:=varFileName, _
ReadOnly:=True, _
AddToMru:=True
'else user canceled out of the dialog box.
End If

 

[Dave Peterson]

If you save the file to a folder that the users only have readonly access
rights, then they'll only be able to open the file in readonly mode--and won't
be prompted.

If you use windows explorer to modify the attributes to be readonly, then excel
will respect this setting, too. The user won't be prompted and the file will
open in readonly mode.

I don't know what you're doing with Access, but this may break it if your Access
code doesn't check for readonly (or doesn't have write access to the folder).

 

[Bill Renaud]

It is TRUE, that files you mark as "Read-Only" on your own computer can be
opened without a prompt.

However, we had an Oracle database where I worked a few years ago. It had a
Visual Basic UI that allowed us to fetch data "snapshots" of the data,
which were written to a multi-tabbed Excel workbook. The files were made
Read-Only by the Visual Basic UI and stored on a network file server. This
was done to prevent us data analysts from accidentally changing the data
while working with the files. These files would always prompt us for the
password, unless we opened them using the "Open Read-Only" option in the
drop-down combo box for the Open button in the Open dialog box.

I used the code that I posted previously to get around this annoying delay
every time we opened one of these files.

 

[Dave Peterson]

I never had any prompts when I changed the attribute manually for files located
on a network drive.

Maybe it was an error in the Oracle code that caused the prompts for you?

 

[Bill Renaud]

<<Maybe it was an error in the Oracle code that caused the prompts for
you?>>

No, it was definitely set by the Visual Basic interface that the developers
used, because the files were initially not marked Read-Only when the
project started. The developers changed to making the Excel files Read-Only
after they realized that we were making a few changes to the workbooks (to
eliminate some merged cells, etc.), then saving them.

 

[Dave Peterson]

I never had a problem when I did it manually.

<<Maybe it was an error in the Oracle code that caused the prompts for
you?>>

No, it was definitely set by the Visual Basic interface that the developers
used, because the files were initially not marked Read-Only when the
project started. The developers changed to making the Excel files Read-Only
after they realized that we were making a few changes to the workbooks (to
eliminate some merged cells, etc.), then saving them.

 

[Bill Renaud]

I should add that this was about 8 years ago, using Excel 97. (Maybe it had
a bug in this regard! )

 

[Bill Renaud]

I think I found what may be causing this prompt for a read-only file
(re-discovered by reviewing another thread)!

In the SaveAs dialog box, click on the Tools button, then select the "Save
Options" command. The Save Options dialog box has the following options
(Excel 2000):

Always create backup (checkbox)

File sharing
Password to open: ________
Password to modify: ________

Read-only recommended (checkbox)

If you enter a password to open, Excel will prompt for a password when
opening the file.

If you check "Read-only recommended", and then open the file using the
password, Excel will prompt with "'Example.xls' should be opened as
read-only unless you need to save changes to it. Open as read-only?". The
message box has Yes, No, and Cancel buttons.

 

[Dave Peterson]

If you use the password to open, you'll always be prompted for that password.

But I've found that if I specified a password to modify and at the same time, I
marked the file readonly in windows explorer (or put the file on a drive that
the user opening the file only had read rights), then excel would never prompt
me for that password to modify.

It makes sense to me. If the file is marked readonly (either manner), then even
if the user specified a password to modify, the file would have to be opened in
readonly mode--to follow the windows setting.

 

[Bill Renaud]

<<If you use the password to open, you'll always be prompted for that
password.>>

Unless you run the following code (given previously for the problem the OP
was trying to solve):

Workbooks.Open FileName:=strFileName, ReadOnly:=True

 

[Dave Peterson]

I meant when you open the workbook interactively--not by code.

 

[Bill Renaud]

<<I meant when you open the workbook interactively--not by code.>>

That's kinda' what I figured, but the word "always" in your previous post
(about being prompted for the password) tripped me up.

(Like a politician: "Never say never!!!")