Creating a Macro that saves over a protected spreadsheet.
 

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"

Creating a Macro that saves over a protected spreadsheet.
 

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.
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"Dolores" wrote:

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"

Creating a Macro that saves over a protected spreadsheet.
 

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)


On Tue, 14 Oct 2008 08:21:02 -0700, Barb Reinhardt
wrote:

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.