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.

<<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:=strFileFil ter, _
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

--
Regards,
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


--
Regards,
Bill Renaud

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).




wrote:

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.

--

Dave Peterson

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.

--
Regards,
Bill Renaud

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 wrote:

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.

--
Regards,
Bill Renaud

--

Dave Peterson

<<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.

--
Regards,
Bill Renaud

I never had a problem when I did it manually.

Bill Renaud wrote:

<<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.

--
Regards,
Bill Renaud

--

Dave Peterson