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


--
Slow1911s
------------------------------------------------------------------------
Slow1911s's Profile: http://www.excelforum.com/member.php...o&userid=31113
View this thread: http://www.excelforum.com/showthread...hreadid=511158

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"
wrote in
message
...

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


--
Slow1911s
------------------------------------------------------------------------
Slow1911s's Profile:
http://www.excelforum.com/member.php...o&userid=31113
View this thread:
http://www.excelforum.com/showthread...hreadid=511158

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?


--
Slow1911s
------------------------------------------------------------------------
Slow1911s's Profile: http://www.excelforum.com/member.php...o&userid=31113
View this thread: http://www.excelforum.com/showthread...hreadid=511158

ThisWorkbook.ChangeFileAccess xlReadWrite


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


"Slow1911s"
wrote in
message
...

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?


--
Slow1911s
------------------------------------------------------------------------
Slow1911s's Profile:
http://www.excelforum.com/member.php...o&userid=31113
View this thread:
http://www.excelforum.com/showthread...hreadid=511158

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.


--
Slow1911s
------------------------------------------------------------------------
Slow1911s's Profile: http://www.excelforum.com/member.php...o&userid=31113
View this thread: http://www.excelforum.com/showthread...hreadid=511158

What errors? Post your code.


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


"Slow1911s"
wrote in
message
...

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.


--
Slow1911s
------------------------------------------------------------------------
Slow1911s's Profile:
http://www.excelforum.com/member.php...o&userid=31113
View this thread:
http://www.excelforum.com/showthread...hreadid=511158

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.


--
Slow1911s
------------------------------------------------------------------------
Slow1911s's Profile: http://www.excelforum.com/member.php...o&userid=31113
View this thread: http://www.excelforum.com/showthread...hreadid=511158

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"
wrote in
message
...


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.


--
Slow1911s
------------------------------------------------------------------------
Slow1911s's Profile:
http://www.excelforum.com/member.php...o&userid=31113
View this thread:
http://www.excelforum.com/showthread...hreadid=511158

Well - it is. See post #1

Is there another method I can use other than saveas?


--
Slow1911s
------------------------------------------------------------------------
Slow1911s's Profile: http://www.excelforum.com/member.php...o&userid=31113
View this thread: http://www.excelforum.com/showthread...hreadid=511158

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



Slow1911s wrote:

Well - it is. See post #1

Is there another method I can use other than saveas?

--
Slow1911s
------------------------------------------------------------------------
Slow1911s's Profile: http://www.excelforum.com/member.php...o&userid=31113
View this thread: http://www.excelforum.com/showthread...hreadid=511158

--

Dave Peterson