Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
No Prompt for Read Only
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
No Prompt for Read Only
<<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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
No Prompt for Read Only
<<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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
No Prompt for Read Only
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
No Prompt for Read Only
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
No Prompt for Read Only
<<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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
No Prompt for Read Only
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
No Prompt for Read Only
I should add that this was about 8 years ago, using Excel 97. (Maybe it had
a bug in this regard! :) ) -- Regards, Bill Renaud |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
No Prompt for Read Only
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. -- Regards, Bill Renaud |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
No Prompt for Read Only
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 wrote: 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. -- Regards, Bill Renaud -- Dave Peterson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
No Prompt for Read Only
<<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 -- Regards, Bill Renaud |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
No Prompt for Read Only
I meant when you open the workbook interactively--not by code.
Bill Renaud wrote: <<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 -- Regards, Bill Renaud -- Dave Peterson |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
No Prompt for Read Only
<<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!!!") -- Smile, Bill Renaud |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Read only prompt? | Excel Discussion (Misc queries) | |||
Read Only Prompt | Excel Discussion (Misc queries) | |||
Password Prompt With Read Only Option | Excel Discussion (Misc queries) | |||
Remove read only prompt | Excel Discussion (Misc queries) | |||
workbook open read only without prompt | Excel Programming |