VBA - Saving as "Read Only" then re-saving over it...

[Panthers0027]

Hello,

I'm using Office 2003. I would like to know how to save a file as Read Only, then re-save over it on a nightly basis.

Every night, I save all the work I do on the shared drive using a "Timer" on Excel.


Sub Timer()
Application.OnTime TimeValue("22:00:00"), "Save_Common_temp"
End Sub

The Macro runs this code:

Sub Save_Common_temp()
 
    Workbooks.Open Filename:= "\\BCAFS01.BCARENA.COM\USERS$\TherrienA\Desktop\Other\Renewals & New Biz\0910 New Biz.xls" _
        , UpdateLinks:=3
 
    ActiveWorkbook.SaveAs Filename:="S:\Common\Temp\Andre\0910 New Biz.xls" 
    ActiveWorkbook.Close
 
End Sub

This basically saves my file over the Network file every night at the same time. The issue is that the shared file is often left open, not allowing me to save over it. If I save it as "Read Only" I can't save over it either.

Any ideas on how to be able to save my updated files on the Shared Network without interruption?

 

[Oorang]

Public Sub Example()
    Const csPathOrg As String = "C:\Test\Test.xls"
    Const csPathNew As String = "C:\Test\Test2.xls"
    'Open file:
    With Workbooks.Open(csPathOrg, 3)
        'Do Stuff:
        .Worksheets(1).Cells(1, 1).Value = "SomeData"
        'You can just close and save at the same time:
        .Close True, csPathNew
    End With
    'Make read only:
    SetAttr csPathNew, GetAttr(csPathNew) Or vbReadOnly
End Sub

 

[Panthers0027]

I tried the code that you recommended. The problem that I'm having at line ".Close True, csPathNew." Because the file in csPathNew is marked as "Read Only", I can't override the file on an everyday basis.

I need to be able to re-save over a "Read Only" file, or find a way to replace yesterday's file with today's new one.

Here's the code that I applied:

Sub Save_NewBiz ()
 
Const csPathOrg As String = "\\BCAFS01.BCARENA.COM\USERS$\TherrienA\Desktop\Other\Renewals & New Biz\0910 New Biz.xls"
Const csPathNew As String = "S:\Common\Temp\Andre\0910 New Biz.xls"
'Open file:
With Workbooks.Open(csPathOrg, 3)
.Close True, csPathNew
End With
'Make read only:
SetAttr csPathNew, GetAttr(csPathNew) Or vbReadOnly
 
End Sub

Any Thoughts?

 

[Oorang]

OIC, no worries. Use:

SetAttr "C:\Test\MyFile.xls",vbNormal

To get rid of the read only attribute.
Then you can either suppress the overwrite alert by doing this:

Application.DisplayAlerts = False

Make sure you turn the alerts back on when you are done. Or you can prevent the alerts by deleting the file that it is not there to be overwritten:

Kill "C:\Test\MyFile.xls

Using either method, you will need to remove the Read-Only attribute first though.

 

[Panthers0027]

Will the "Kill" statement work if someone has the file open? If so, it's exactly what I was looking for!

It seems to work on both "vbNormal" and "vbReadOnly" files.

 

[Oorang]

It will if they have it open read only. There are two issue that could prevent deletion, first it being read-only, which we fix with setattr. The second is the user having the file locked by opening it for write access. But as the file was read-only in the first place, odd are if they have it open, it will be in read-only mode. This will allow the file to be deleted without the user noticing.

Another technique to consider would be to prevent the user from being in the file at all

Private Sub OpenAsClone()
    'Aaron Bush 12/30/2009
    Dim sPath As String
    If Not ThisWorkbook.Name Like "local_*" Then
        sPath = Environ$("TEMP") & "\local_" & ThisWorkbook.Name
        ThisWorkbook.SaveCopyAs sPath
        Excel.Workbooks.Open sPath
        ThisWorkbook.Close False
    End If
End Sub

 

[Panthers0027]

Thanks Oorang, you've been a tremendous help! I went ahead and saved it as "read only," and will kill and replace the files every night using a timer.

Thanks again!
Andre