PDA

View Full Version : add save macro



niyrho
11-16-2008, 11:21 AM
I have a macro set up to save a worksheet. It takes two cell values to use as a file name and saves to a spacific location. It works as long as it's a new save. Sometimes I'll have to open an old one, add to it, and resave it, but my macro won't do that. Can anyone help me?

The code I'm using now is:

Private Sub SaveButton1_Click()
On Error GoTo Exits
cancel = True
Application.EnableEvents = False
With ActiveSheet
ActiveWorkbook.SaveCopyAs "C:\Time Sheets\" & Format(.Range("A12"), " yy-mm-dd") & .Range("H10") & ".xls"
End With
Exits:
Application.EnableEvents = True
End Sub

Bob Phillips
11-16-2008, 11:51 AM
Works fine for me. What happens for you?

niyrho
11-16-2008, 12:19 PM
My problem is that when I open a file that it already saved it won't overwrite it when I try to save it again.

I'm using this for a time sheet. So, every monday after you fill it out and hit save it makes a new file and uses monday's date for the file name. Then tuesday you'd have to open that file, fill it out for that day and then save it again. And so on through the week. Then next monday, you'd enter a different date and that'll make it creat a new file.

My problem is that when I open it to fill out tuesday or wednesday or whatever, it won't overwrite it when I hit the save button. It can only create new files.

MaximS
11-16-2008, 01:17 PM
try this:


Private Sub SaveButton1_Click()
On Error GoTo Exits
Cancel = True
Application.EnableEvents = False

Dim YourFile As String

YourFile = "C:\Time Sheets\" & Format(.Range("A12"), " yy-mm-dd") & _
.Range("H10") & ".xls"

If File_Exists(YourFile) Then
Application.DisplayAlerts = False
With ActiveSheet
ActiveWorkbook.Save YourFile
End With
Application.DisplayAlerts = True
Else
With ActiveSheet
ActiveWorkbook.SaveCopyAs YourFile
End With
End If

Exits:
Application.EnableEvents = True
End Sub


Function File_Exists(FileName As String) As Boolean
File_Exists = (Dir(FileName) > "")
End Function

georgiboy
11-16-2008, 01:30 PM
Private Sub SaveButton1_Click()
On Error GoTo Exits
cancel = True
Application.EnableEvents = False
Application.DisplayAlerts = False
With ActiveSheet
ActiveWorkbook.SaveAs "C:\Time Sheets\" & Format(.Range("A12"), " yy-mm-dd") & .Range("H10") & ".xls"
End With
Application.DisplayAlerts = True
Exits:
Application.EnableEvents = True
End Sub