Thread: Help with xlReadOnly

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

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"
<Slow1911s.2311db_1139597403.1758@excelforum-nospam.com> wrote in
message
news:Slow1911s.2311db_1139597403.1758@excelforum-nospam.com...
>
> 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?

ThisWorkbook.ChangeFileAccess xlReadWrite


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


"Slow1911s"
<Slow1911s.2312ao_1139598628.9955@excelforum-nospam.com> wrote in
message
news:Slow1911s.2312ao_1139598628.9955@excelforum-nospam.com...
>
> 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.

What errors? Post your code.


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


"Slow1911s"
<Slow1911s.2313wy_1139600706.6559@excelforum-nospam.com> wrote in
message
news:Slow1911s.2313wy_1139600706.6559@excelforum-nospam.com...
>
> 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.

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"
<Slow1911s.23152n_1139602203.6334@excelforum-nospam.com> wrote in
message
news:Slow1911s.23152n_1139602203.6334@excelforum-nospam.com...
>
>>
>> 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?

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