Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Before Save code | Excel Worksheet Functions | |||
Totally Disabling (^ save ) (Save as) and Save Icon – Which code do I use: | Excel Programming | |||
Placing a code before Save & Save As | Excel Programming | |||
Save without VBA code? | Excel Programming | |||
save without code | Excel Programming |