Help with xlReadOnly

[Slow1911s]

code

With ThisWorkbook
.Save
.ChangeFileAccess xlReadOnly
Kill .FullName
.saveas Filename:=newfilename, FileFormat:=xlNormal
End With

This is part of some coding that utilizes a naming convention based on
worksheet entries. As I understand it, since it is changed to Read
Only, it can't be saved again with the same name. If the sheet is
saved with the naming convention, edited and this code tries to execute
again, I get a

-Run-time error 1004, Cannot Save as that name. Document ws opened as
read-only.-

Is the -.ChangeFileAccess xlReadOnly- necessary. Can I use
xlReadWrite? I also use a password.

TIA

 

[Chip Pearson]

I believe that's code I posted a few days ago. You need the
xlReadOnly in order to allow the file to delete itself.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Slow1911s"
message

 

[Slow1911s]

Yes, it sure is and a belated thank you - you've helped me greatly.

Is there another line I can put in that changes the access back to
normal?

 

[Chip Pearson]

ThisWorkbook.ChangeFileAccess xlReadWrite


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Slow1911s"
message
news:[email protected]...

 

[Slow1911s]

I tried inserting that line after the Kill line and before the With
ThisWorkBook line and I'm getting still getting errors. Ugh.

Thanks for your help.

 

[Chip Pearson]

What errors? Post your code.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Slow1911s"
message
news:[email protected]...

 

[Slow1911s]

ThisWorkBook.ChangeFileAccess xlReadWrite
With ThisWorkbook
.Save
.ChangeFileAccess xlReadOnly
Kill .FullName
.saveas Filename:=newfilename, FileFormat:=xlNormal
End With

and...

With ThisWorkbook
.Save
.ChangeFileAccess xlReadOnly
Kill .FullName
.ChangeFileAccess xlReadWrite
.saveas Filename:=newfilename, FileFormat:=xlNormal
End With

Still getting runtime 1004 error - method failed.

 

[Chip Pearson]

Actually, you don't need to change the file access to
xlReadWrite. The new file will be read/write, not read-only.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





"Slow1911s"
message

 

[Slow1911s]

Well - it is. See post #1

Is there another method I can use other than saveas?

 

[Dave Peterson]

If you do your saveAs first, the active workbook gets that new name. And you
can delete the old file without changing the readonly stuff.

dim OldName as string
dim newfilename as string
....
with thisworkbook
oldname = .fullname
'make sure newfilename isn't the same as oldname!
.saveas filename:=newfilename, FileFormat:=xlNormal
kill oldname
end with

(I didn't test this--so watch out!!)