Save As code

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Aaron - 21 Mar 2006 16:07 GMT

Hi,

I am looking to automate my daily Save As. I create an updated workbook
every day that needs to be saved with a name and Date. The name XYZ is the
same, but it is followed with a Date that is located in Cell B2 of sheet ABC.
Does anyone know if I can create a macro that will save my file with the
name and date located in cell b2? Thank you in advance.

Reply to this Message

Kevin B - 21 Mar 2006 16:40 GMT

The following macro verifies that there is a date in B2 in the sheet named
ABC, and if not it uses the current date. The date is formatted as text with
dashes between the month-day-year as the "/" is an invalid character in a
filename:

Sub SaveWithDate()
'
Dim wb As Workbook
Dim ws As Worksheet
Dim varVal As Variant
Dim strFileName As String

Set wb = ActiveWorkbook
Set ws = wb.Worksheets("ABC")
varVal = ws.Range("B2").Value

If IsDate(varVal) Then
strFileName = "DataFile_" & Format(CStr(varVal), "mm-dd-yyyy") &
".xls"
Else
strFileName = "DataFile_" & Format(CStr(Date), "mm-dd-yyyy") & ".xls"
End If

ActiveWorkbook.SaveAs Filename:=strFileName

Set wb = Nothing
Set ws = Nothing

End Sub

Signature

Kevin Backmann

> Hi,
>
[quoted text clipped - 3 lines]
> Does anyone know if I can create a macro that will save my file with the
> name and date located in cell b2? Thank you in advance.

Reply to this Message

Aaron - 21 Mar 2006 16:54 GMT

Hi again, and thanks so much. That worked great. If possible, could you
please tell me how to add a directory...meaning I would like to save it to a
different folder...directory is something like Departments/Cutting/Old
Schedules

Thank you much

> The following macro verifies that there is a date in B2 in the sheet named
> ABC, and if not it uses the current date. The date is formatted as text with
[quoted text clipped - 33 lines]
> > Does anyone know if I can create a macro that will save my file with the
> > name and date located in cell b2? Thank you in advance.

Reply to this Message

Aaron - 21 Mar 2006 17:01 GMT

Oops, I figured it out...just needed to insert the chdir. Thanks much,

Aaron

> Hi again, and thanks so much. That worked great. If possible, could you
> please tell me how to add a directory...meaning I would like to save it to a
[quoted text clipped - 40 lines]
> > > Does anyone know if I can create a macro that will save my file with the
> > > name and date located in cell b2? Thank you in advance.

Reply to this Message

Kevin B - 21 Mar 2006 17:06 GMT

You can create a string and contcatenate the path to the filename.

Declare a string variable, named strPath in my example and assign the path
string to the variable. Be sure to include the backslash at the end of the
path.

Dim strPath as string

strPath = "C:\Documents\Excel\My Data\"

Then modify the command that saves the file to:

ActiveWorkbook.SaveAs Filename:=strPahtName & strFileName

Signature

Kevin Backmann

Reply to this Message