Save spreadsheet into folder based on today's Date?

[nbaj2k]

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*

 

[LitVilkas]

' 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]

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")

ActiveWorkbook.SaveAs filename:= _
""G:\Reports\Daily Reports\" & today & "\filename"", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=No
_
, CreateBackup:=False

 

[slo]

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]

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