2003 up-to-date
How to save current project with current project filename + Current
Date + Current Time
? Date() in Immediate Window - Returns date with "/" which cannot be
used in a filename.
Therefore, how can I SaveAs a CurrentProject.name + Date + Time in a
"new" filename?
Or kinda-like -
DoCmd.Save CurrentProject.Path & "\" & CurrentProject.name &
Date????? & Time ?????
TIA EagleOne
Here's some code I put together to do something similar saving email as
text from Outlook. I'm going to remove the Outlook specific code as it's
not particularly relevant.
There are a variety of ways to break apart date information: Have you
looked at all the various date/time functions in the VBA help? Also, is
it important for the date-time information to be recognizable? In the
sample below I'm using just enough of the system date/time value to
guarantee a unique filename. I've since thought of other ways to do it,
but at the time I wrote a FRA function to extract just the fractional
value from a number.
As to dealing with the slash, I wrote a function to remove poison
characters from a string.
Hope this is useful!
::::::::::::: Begin VBA code :::::::::::::::::::::::
Public Const HomeEmail = "C:\long path name\to\folder I save to\"
rem function to return fractional portion of a number
Public Function Fra(ByVal num As Double) As Double
Fra = num - (Fix(num))
End Function
rem portion of code to save email message as text
Public Sub SaveAsTXT()
' Save selected mail item as text to home email folder.
' Add now() stamp to prevent duplicate filenames
rem I removed code to get email message.
"myItem.Subject" is the string containing the email subject,
which I use as the filename. There are some debug.print lines
commented out that I used during testing:
With myItem
' Debug.Print "Subject: ", .Subject
' Debug.Print "New name: ", .Subject & _
Format(Now Mod 1000 + Fra(Now), " 000.00000") & ".txt"
strName = HomeEmail & ParseFileName(.Subject) & _
Format(Now Mod 1000 + Fra(Now), " 000.00000") & ".txt"
' Debug.Print strName
rem strName is the fully qualified pathname of the new file. At this
point I inserted the Outlook SaveAS command.
End With
'Debug.Print "End Save as Text "; time
Set myItem = Nothing
Set SavedAsText = Nothing
End Sub
rem And this is the function to remove poison characters from a string.
I replace the poison character with a space, then finish with a test for
any double spaces and remove any found.
Public Function ParseFileName(ByVal strName As String) As String
' remove poison characters from a filename string
' \ / : * ? " < > |
' then replace any double spaces with single spaces
Dim Poison As Variant 'Array of Poison characters
Dim c1 As Integer 'instr start parameter
Poison = Array("\", "/", ":", "*", "?", """", "<", ">", "|", " ")
If Len(strName) > 0 Then
For Each c In Poison
c1 = 1 'Search from 1st character
Do
c1 = InStr(c1, strName, c, vbBinaryCompare)
If c1 Then
Mid(strName, c1, 1) = " "
If Len(c) > 1 Then
strName = Left(strName, c1) & Mid(strName, c1 + Len(c))
End If
End If
Loop Until c1 = 0
Next c
ParseFileName = strName
End If
End Function
::::::::::::: End VBA code :::::::::::::::::::::::
