Including date in output file name

  • Thread starter Thread starter VS
  • Start date Start date
V

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!
 
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
 
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"
 
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)?
 
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>
 
Back
Top