Creating a Macro that saves over a protected spreadsheet.

[Dolores]

Is there a way to create a macro that can save over a protected file.


I used the macro record tool that comes with excel, to record the save as
function, however the document I am trying to save over is a protected file,
so I keep getting an error. Is there some code that can be added to my macro
listed below so that I do not have to manually unprotect my file before I run
the macro?

Sub saveas()
'
' saveas Macro
' Macro recorded 9/30/2008 by dgomez
'
ChDir "F:\Illinois-Indy Sales"
ActiveWorkbook.saveas Filename:="F:\Illinois-Indy Sales\Bid
Register.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

when I run the macro I get the following error "Cannot access read-only
document "Bid Register.xls"

 

[Barb Reinhardt]

I'm thinking that the file is set up with an attribute of READ ONLY. You
can find this by right clicking on the file name and selecting PROPERTIES.

I don't know of a way to change this programmatically, but I've never looked
into it either. Maybe someone else can assist.

 

[Chip Pearson]

I don't know of a way to change this programmatically,

Code like the following will set or clear the read-only flag for a
file.

Dim FName As String
FName = "C:\DirTree\Readme.txt"
' set read only flag
SetAttr FName, vbNormal + vbReadOnly
' clear read only flag
SetAttr FName, vbNormal

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)