' 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