check file in folder and overwrite

[rakesh seebaruth]

Hi,

I have the following code

Sub saveas()
mydrive = "C:"
mydir = "excel"
myname = Sheets("sheet1").Range("e5")'
ms = mydrive & "\" & mydir & "\" & myname & ".xls"
Application.DisplayAlerts = False
ActiveWorkbook.SaveCopyAs Filename:=ms
Application.DisplayAlerts = True
End Sub

I want vba to check in C:excel if file("e5") exists .If exists overwrite,and protect with password ,and save it.

If does not exists then save it as save as .

I'm using excel 2003

thnaks

 

[wigi]

Hello

Consider using:

Sub saveas()
    ms = "C:\excel\" & Sheets("sheet1").Range("E5").Text & ".xls"
    If Len(Dir(ms)) Then Kill ms
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveCopyAs Filename:=ms
    Application.DisplayAlerts = True
End Sub

 

[rakesh seebaruth]

thanks dear.

now how to protect the file ?

 

[wigi]

You can use the .Protect method on a workbook, like ActiveWorkbook.

 

[rakesh seebaruth]

Re:runtime error

Hi wigi,

Thanks for your post. i refer to your first post.

when i save a new file(example abc.xls) , it's ok.

When i made changes in "abc.xls" and save it.It is supposed to kill it but i get runtime error 70.permission denied

please help

 

[wigi]

Re: runtime error

Hi wigi,

Thanks for your post. i refer to your first post.

when i save a new file(example abc.xls) , it's ok.

When i made changes in "abc.xls" and save it.It is supposed to kill it but i get runtime error 70.permission denied

please help

Hi

So, basically, you are trying to kill (delete) a file while the file is opened? That will not be possible. You could save a copy from that file to another location and/or filename.

 

[rakesh seebaruth]

Hi there,

Thanks for your reply.

If i close the file ,delete it and save it

is it going to work.

let's say :-
If Len(Dir(ms)) Then close file , kill and save)my code is not good of course.

please help