Urgent problem saving a file read only

[DoctorV]

I have an Excel workbook that performs a bunch of steps with Microsof
Query. It has code so it can be saved as a read only file and MOS
IMPORTANTLY it needs to save the file over the top of the existing fil
even if a user has this ReadOnly File named MyFile open open. This ha
to work somehow because they will be saving a fresh copy of this fil
every day. How can I get this to overwrite this file as Read Only eve
if a user has it opened read only Thanks URGENT

Here is my code
Dim fs, f, filespec As String
'filespec
"S:\Links\BISCatalog\TrafficLightReport\Traffic_Light_Report_ReadOnly.xls"
filespec = "O:\Taber_Excel2\MyFile.xls"
Application.DisplayAlerts = False
RemoveAllMacros ActiveWorkbook
If Dir(filespec) <> "" Then
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(filespec)
f.Attributes = 0
Sheets("MainForm").Select
ActiveWindow.SelectedSheets.Visible = False

End If

ActiveWorkbook.SaveAs Filename:=filespec

f.Attributes = 1

ActiveWorkbook.Close

Application.DisplayAlerts = True
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(filespec

 

[Frank abel]

Hi
I doubt this is possible (prevented by the Operating
System). If a file is opened by another user this can't be
over-written (in addition what should happen for the user
who has opened this file: automatic closing of Excel or
automatic replacing?).

So AFAIK this is not Excel specific but restricted by the
OS
Some ideas for a workaround:
-maybe use a shared Excel file (and live with the
restrictions of this feature)
- use a different type of application (e.g. database)

 

[keepITcool]

ActiveWorkbook.SaveAs Filename:=filespec
f.Attributes = 1
ActiveWorkbook.Close

[snip]


Untested but could it be following?

after you use saveas you have to close it first,
and THEN change the readonly property of the file.

(it's not the user's lock that's bothering you,
it's your own


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[keepITcool]

Hi
I doubt this is possible (prevented by the Operating
System). If a file is opened by another user this can't be
over-written (in addition what should happen for the user
who has opened this file: automatic closing of Excel or
automatic replacing?).

So AFAIK this is not Excel specific but restricted by the
OS
Some ideas for a workaround:
-maybe use a shared Excel file (and live with the
restrictions of this feature)
- use a different type of application (e.g. database)

Frank.. frankly i think you're confused !

if any program (including Excel) opens a file as readonly
it IS readonly.. and any program or user can delete it
(if it has permission from the filesystem in that directory)

Test:
new workbook
save as ro.xls
close
open readonly ro.xls (in xlXP dropdown the open button)
(in xl97 use option button (topright)

in explorer => delete the file.



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Frank Kabel]

Hi
you're right (to hot inside the office probably). Didn't
read that the files are opened 'read-only'. So the OP
should try your suggestion

P.S.: Still I don't think this is a good design