Including date in output file name

[VS]

Hi,

I have a macro that executes a query, then exports the
data into Excel using "OutputTo" action.

What I need is to have today's date included in the file
name but I have no idea how to do so.

I have set windows scheduler to run this macro daily and
I don't want to overide previously exported files. Any
suggestions?

Thanks!

 

[Joe Fallon]

Learn VBA?

I haven't used macros in years, but if you can build a string for the file
name then it could work.
strFileName = Date() & "MyFile.txt"

Should give you:
07/18/2003MyFile.txt

I use code like this to re-name the old file using a date/time and the old
name:
If FileExists(strFullPath) = True Then
Dim NewName As String
NewName = Left$(strFullPath, Len(strFullPath) - Len(Dir(strFullPath))) &
Format(Now(), "yyyymmddhhnnss") & " " & Right(strFullPath,
Len(Dir(strFullPath)))
Name strFullPath As NewName
End If

=====================================
'you can add this code to a module:
Public Function FileExists(strPath As String, Optional lngType As Long) As
Boolean
On Error Resume Next
FileExists = Len(Dir(strPath, lngType)) > 0
End Function

 

[Ken Snell]

You can build a file name and path in the OutputTo macro action. Simply use
an expression similar to what you show for the variable expression in the
"Output File" box:

="C:\MyFolderName\" & Date() & "MyFile.txt"

 

[Douglas J. Steele]

Joe: You can't have slashes in a file name. You have to use Format to
replace the slashes with dashes, or periods, or just leave them out. (i.e.:
what you did in your rename routine)

And just out of curiosity, is there any reason why you're using
Right(strFullPath, Len(Dir(strFullPath)))? Doesn't that give you the same as
Dir(strFullPath)?

 

[Joe Fallon]

Doug,
You are right.
I typed the "sample" result w/o thinking.

But my code uses format and yyyymmddhhnnss so....

"Doesn't that give you the same as Dir(strFullPath)?"
I don't know.
1. Some reason I don't recall?
2. I never tried it?! <g>