Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save spreadsheet into folder based on today's Date?
I wondered if someone knew how to do this. I have a macro setup that runs a query, comes back with a table, and saves the file at the end. Right now I have it saving as it is shown below. The code that is bolded, I wanted to know if there is a way to make it save in a newly created folder with today's date, such as D:\Reports\Friday Reports\8-4-06 but if i ran it another day it would have that date in it. I'm not exactly sure how to put that in, as any code I tried would not work. I know it should be a simple line of code, if anyone knew what to put it would be greatly appreciated Thanks, ~J ================================================ Range("A1").Select Rows("1:1").Select Selection.Insert Shift:=xlDown Range("A1").Select Rows("1:1").Select Selection.Insert Shift:=xlDown Range("A1").Select ActiveCell.FormulaR1C1 = "Employee_" & Format(Date, "mm-dd-yy") & ".xls" Range("A1").Select Dim filename As String filename = Range("A1") ' Test ChDir "D:\Reports\Friday Reports\" ActiveWorkbook.SaveAs filename:= _ *"D:\Reports\Friday Reports\" & filename, FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=No _ , CreateBackup:=False* -- nbaj2k ------------------------------------------------------------------------ nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480 View this thread: http://www.excelforum.com/showthread...hreadid=568306 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save spreadsheet into folder based on today's Date?
' Define variable with today's date
today = Date 'Create directory MkDir "D:\Reports\Friday Reports\" & today 'Save file File_Address = ""D:\Reports\Friday Reports\" & today & "\My Files.xls" ActiveWorkbook.SaveAs Filename:=File_Address, Password:="" It seems that you cannot save directly into a nonexistent directory from VBA nbaj2k wrote: I wondered if someone knew how to do this. I have a macro setup that runs a query, comes back with a table, and saves the file at the end. Right now I have it saving as it is shown below. The code that is bolded, I wanted to know if there is a way to make it save in a newly created folder with today's date, such as D:\Reports\Friday Reports\8-4-06 but if i ran it another day it would have that date in it. I'm not exactly sure how to put that in, as any code I tried would not work. I know it should be a simple line of code, if anyone knew what to put it would be greatly appreciated Thanks, ~J ================================================ Range("A1").Select Rows("1:1").Select Selection.Insert Shift:=xlDown Range("A1").Select Rows("1:1").Select Selection.Insert Shift:=xlDown Range("A1").Select ActiveCell.FormulaR1C1 = "Employee_" & Format(Date, "mm-dd-yy") & ".xls" Range("A1").Select Dim filename As String filename = Range("A1") ' Test ChDir "D:\Reports\Friday Reports\" ActiveWorkbook.SaveAs filename:= _ *"D:\Reports\Friday Reports\" & filename, FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=No _ , CreateBackup:=False* -- nbaj2k ------------------------------------------------------------------------ nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480 View this thread: http://www.excelforum.com/showthread...hreadid=568306 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save spreadsheet into folder based on today's Date?
Here is my code now, still not working. I have my filename declared earlier, it has a problem with the part that is bold. It says expected end of statement, am I missing something? Thanks, ~J today = Date Dim filename As String MkDir "G:\Reports\Daily Reports\" & today filename = Range("A1") [b]ActiveWorkbook.SaveAs filename:= _ ""G:\Reports\Daily Reports\" & today & "\filename"", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=No _ , CreateBackup:=False[b] -- nbaj2k ------------------------------------------------------------------------ nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480 View this thread: http://www.excelforum.com/showthread...hreadid=568306 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save spreadsheet into folder based on today's Date?
Try this:
Sub SaveToDatedFolder() MyDate = Trim(VBA.Format(Now(), "MMDDYYHMM")) Application.DisplayAlerts = False MkDir "\\server\share\exsistingfolder\" & MyDate ActiveWorkbook.SaveAs Filename:="\\server\share\exsistingfolder\" & MyDate & "\filename.xls" ActiveWorkbook.Close False Application.DisplayAlerts = True End Sub nbaj2k wrote: Here is my code now, still not working. I have my filename declared earlier, it has a problem with the part that is bold. It says expected end of statement, am I missing something? Thanks, ~J today = Date Dim filename As String MkDir "G:\Reports\Daily Reports\" & today filename = Range("A1") [b]ActiveWorkbook.SaveAs filename:= _ ""G:\Reports\Daily Reports\" & today & "\filename"", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=No _ , CreateBackup:=False[b] -- nbaj2k ------------------------------------------------------------------------ nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480 View this thread: http://www.excelforum.com/showthread...hreadid=568306 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save spreadsheet into folder based on today's Date?
Below is what I have right now, I can't figure out how to put the dat in the filename also. I wanted it to say DailyReports-Aug-04-06.xl (with the date of course being the day prior of the current day. I' not sure where to move the quotes on the bolded line so that I ca include that. Is there a way to do that? Thanks, ~J MyDate = Trim(VBA.Format(Now(), "MM-DD-YY")) Application.DisplayAlerts = False MkDir "G:\DailyReportsTest\" & MyDate ACTIVEWORKBOOK.SAVEAS FILENAME:=\"G:\DAILYREPORTSTEST\\" & _ MYDATE & \"\DAILYREPORT.XLS\ 'ActiveWorkbook.Close False Application.DisplayAlerts = Tru -- nbaj2 ----------------------------------------------------------------------- nbaj2k's Profile: http://www.excelforum.com/member.php...fo&userid=3648 View this thread: http://www.excelforum.com/showthread.php?threadid=56830 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I'd like to conditionally format a cell based on today's date | Excel Discussion (Misc queries) | |||
Conditional Formatting based on Today's Date | Excel Discussion (Misc queries) | |||
multiple If's based on today's date | Excel Worksheet Functions | |||
how do you conditional format based upon today's date? | Excel Worksheet Functions | |||
Save Workbook (Blist) as today's date | Excel Programming |