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_Li ght_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

--
Message posted from http://www.ExcelForum.com

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)


-----Original Message-----
I have an Excel workbook that performs a bunch of steps
with Microsoft
Query. It has code so it can be saved as a read only file
and MOST
IMPORTANTLY it needs to save the file over the top of the
existing file
even if a user has this ReadOnly File named MyFile open
open. This has
to work somehow because they will be saving a fresh copy
of this file
every day. How can I get this to overwrite this file as
Read Only even
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_L ight_Repo
rt_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)


---
Message posted from http://www.ExcelForum.com/

.

DoctorV wrote:
[snip]
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

"Frank abel" wrote:

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

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

-----Original Message-----
"Frank abel" wrote:

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
.