Save As code
 

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.

Save As code
 

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

--
Kevin Backmann


"Aaron" wrote:

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.

Save As code
 

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



"Kevin B" wrote:

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

--
Kevin Backmann


"Aaron" wrote:

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.

Save As code
 

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

Aaron

"Aaron" wrote:

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



"Kevin B" wrote:

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

--
Kevin Backmann


"Aaron" wrote:

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.

Save As code
 

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
--
Kevin Backmann


"Aaron" wrote:

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



"Kevin B" wrote:

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:=strPahtName & strFileName

Set wb = Nothing
Set ws = Nothing

End Sub

--
Kevin Backmann


"Aaron" wrote:

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.