I can't find the right code in any posts for this type of save. I just want
to save the current file to a specified location without specifying the
filename in the macro.
In the macro, the active file is a template that needs to be saved as a
spreadsheet. Ideally, I want to replace an existing file ("FILM 08-30-06")
using the current date in the format shown ("FILM 09-05-06").

tia -kelli

I should mention that I want to choose a file to be replaced from the files
in the save-to location, and have the current file name itself using the text
portion of that filename, and replacing the date portion with the current
date.



"KelliInCali" wrote:

I can't find the right code in any posts for this type of save. I just want
to save the current file to a specified location without specifying the
filename in the macro.
In the macro, the active file is a template that needs to be saved as a
spreadsheet. Ideally, I want to replace an existing file ("FILM 08-30-06")
using the current date in the format shown ("FILM 09-05-06").

tia -kelli

This will do the save with the date included in the filename:

Sub gsnu()
Dim s1 As String, s2 As String, s3 As String, s4 As String
s1 = "C:\Documents and Settings\Owner\Desktop\"
s2 = "FILM " & Format(Date, "mm-dd-yy")
s3 = ".xls"
s4 = s1 & s2 & s3
ChDir s1
ActiveWorkbook.SaveAs Filename:=s4
End Sub

You, of course, will replace s1 with your directory. The macro also does
not delete previously saved versions.
--
Gary's Student


"KelliInCali" wrote:

I should mention that I want to choose a file to be replaced from the files
in the save-to location, and have the current file name itself using the text
portion of that filename, and replacing the date portion with the current
date.



"KelliInCali" wrote:

I can't find the right code in any posts for this type of save. I just want
to save the current file to a specified location without specifying the
filename in the macro.
In the macro, the active file is a template that needs to be saved as a
spreadsheet. Ideally, I want to replace an existing file ("FILM 08-30-06")
using the current date in the format shown ("FILM 09-05-06").

tia -kelli

Something like:
ActiveWorkbook.SaveAs Filename:="C:\YourPath\FILM " &
Format(Date(),"mm-dd-yy")
or more dynamic like:
ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & _
Application.PathSeparator & Left(ActiveWorkbook.Name, _
InStr(1,ActiveWorkbook.Name," ") - 1) & Format(Date(),"mm-dd-yy")

Charles

KelliInCali wrote:
I can't find the right code in any posts for this type of save. I just want
to save the current file to a specified location without specifying the
filename in the macro.
In the macro, the active file is a template that needs to be saved as a
spreadsheet. Ideally, I want to replace an existing file ("FILM 08-30-06")
using the current date in the format shown ("FILM 09-05-06").

tia -kelli

Thanks Charles and GS, but these don't seem to do it. "FILM" won't always be
"FILM". I run this same macro for various data sets so I need to be able to
manually choose the existing file to be replaced each time and have the text
portion of the name be derived from that file:
(FILM 08-30-06, ART 08-30-06, ELEC 08-30-06, etc.)



"Die_Another_Day" wrote:

Something like:
ActiveWorkbook.SaveAs Filename:="C:\YourPath\FILM " &
Format(Date(),"mm-dd-yy")
or more dynamic like:
ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & _
Application.PathSeparator & Left(ActiveWorkbook.Name, _
InStr(1,ActiveWorkbook.Name," ") - 1) & Format(Date(),"mm-dd-yy")

Charles

KelliInCali wrote:
I can't find the right code in any posts for this type of save. I just want
to save the current file to a specified location without specifying the
filename in the macro.
In the macro, the active file is a template that needs to be saved as a
spreadsheet. Ideally, I want to replace an existing file ("FILM 08-30-06")
using the current date in the format shown ("FILM 09-05-06").

tia -kelli

Well, O.K then:


Sub gsnu()
Dim s1 As String, s2 As String, s3 As String, s4 As String
s1 = "C:\Documents and Settings\Owner\Desktop\"
s2 = Application.InputBox(prompt:="File Name?", Type:=2)
s2 = s2 & " " & Format(Date, "mm-dd-yy")
s3 = ".xls"
s4 = s1 & s2 & s3
ChDir s1
ActiveWorkbook.SaveAs Filename:=s4
End Sub
--
Gary's Student


"KelliInCali" wrote:

Thanks Charles and GS, but these don't seem to do it. "FILM" won't always be
"FILM". I run this same macro for various data sets so I need to be able to
manually choose the existing file to be replaced each time and have the text
portion of the name be derived from that file:
(FILM 08-30-06, ART 08-30-06, ELEC 08-30-06, etc.)



"Die_Another_Day" wrote:

Something like:
ActiveWorkbook.SaveAs Filename:="C:\YourPath\FILM " &
Format(Date(),"mm-dd-yy")
or more dynamic like:
ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & _
Application.PathSeparator & Left(ActiveWorkbook.Name, _
InStr(1,ActiveWorkbook.Name," ") - 1) & Format(Date(),"mm-dd-yy")

Charles

KelliInCali wrote:
I can't find the right code in any posts for this type of save. I just want
to save the current file to a specified location without specifying the
filename in the macro.
In the macro, the active file is a template that needs to be saved as a
spreadsheet. Ideally, I want to replace an existing file ("FILM 08-30-06")
using the current date in the format shown ("FILM 09-05-06").

tia -kelli

Nope... still doesn't work for me. Everything works until the end, but then
it has a problem with an earlier line of code. How about this... How do I
just prompt the "Save As" window to come up? The user will choose the place
and name manually, but I want the "save" process to begin at the end of my
macro.

thanks,
kelli




"Gary''s Student" wrote:

Well, O.K then:


Sub gsnu()
Dim s1 As String, s2 As String, s3 As String, s4 As String
s1 = "C:\Documents and Settings\Owner\Desktop\"
s2 = Application.InputBox(prompt:="File Name?", Type:=2)
s2 = s2 & " " & Format(Date, "mm-dd-yy")
s3 = ".xls"
s4 = s1 & s2 & s3
ChDir s1
ActiveWorkbook.SaveAs Filename:=s4
End Sub
--
Gary's Student


"KelliInCali" wrote:

Thanks Charles and GS, but these don't seem to do it. "FILM" won't always be
"FILM". I run this same macro for various data sets so I need to be able to
manually choose the existing file to be replaced each time and have the text
portion of the name be derived from that file:
(FILM 08-30-06, ART 08-30-06, ELEC 08-30-06, etc.)



"Die_Another_Day" wrote:

Something like:
ActiveWorkbook.SaveAs Filename:="C:\YourPath\FILM " &
Format(Date(),"mm-dd-yy")
or more dynamic like:
ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & _
Application.PathSeparator & Left(ActiveWorkbook.Name, _
InStr(1,ActiveWorkbook.Name," ") - 1) & Format(Date(),"mm-dd-yy")

Charles

KelliInCali wrote:
I can't find the right code in any posts for this type of save. I just want
to save the current file to a specified location without specifying the
filename in the macro.
In the macro, the active file is a template that needs to be saved as a
spreadsheet. Ideally, I want to replace an existing file ("FILM 08-30-06")
using the current date in the format shown ("FILM 09-05-06").

tia -kelli

Maybe...

Option Explicit
Sub testme()

Dim myNewFileName As String
Dim myPath As String
Dim wkbk As Workbook

Set wkbk = ActiveWorkbook

myPath = wkbk.Path & "\"
'or set it to the path you want

myNewFileName = wkbk.Name

'strip off .xls
If LCase(Right(myNewFileName, 4)) = ".xls" Then
myNewFileName = Left(myNewFileName, Len(myNewFileName) - 4)
End If

'strip off date
If Right(myNewFileName, 9) Like " ##-##-##" Then
myNewFileName = Left(myNewFileName, Len(myNewFileName) - 9)
End If

'add back space and date
myNewFileName = myNewFileName & " " & Format(Date, "mm-dd-yy") & ".xls"

'MsgBox myNewFileName 'just to verify

Application.DisplayAlerts = False 'overwrite without prompt
wkbk.SaveAs Filename:=myPath & myNewFileName, FileFormat:=xlWorkbookNormal
Application.DisplayAlerts = True

End Sub


KelliInCali wrote:

I can't find the right code in any posts for this type of save. I just want
to save the current file to a specified location without specifying the
filename in the macro.
In the macro, the active file is a template that needs to be saved as a
spreadsheet. Ideally, I want to replace an existing file ("FILM 08-30-06")
using the current date in the format shown ("FILM 09-05-06").

tia -kelli

--

Dave Peterson