Put date stamp on file name using VBA
 

Hello,

I desperately need help on this one. Below I have a command button coded in
Excel to copy files from the source folder to the destination folder, which
works fine. This coding is from Ron de Bruin website, which is an excellent
resource. My problem is I want to copy the files from the source folder
into the destination folder, but put a date stamp at the end of the file
name. For example "claim.xls" from the source folder and I want it to
change it to "claim01032007.xls. I want the date stamp done in every file
copied to the destination folder. Is this possible? I have many files from
the source folder. Any tips or website to visit will be appreciated. Thank
you in advance.


Private Sub cmdMoveFiles_Click()

Dim FSO As Object
Dim FromPath As String
Dim ToPath As String
Dim FileExt As String

FromPath = "D:\Test" 'From Path Folder
ToPath = "D:\Audits\" 'To Path - Destination Folder
FileExt = "*.xls"


If Right(FromPath, 1) < "\" Then
FromPath = FromPath & "\"
End If

Set FSO = CreateObject("scripting.filesystemobject")

If FSO.FolderExists(FromPath) = False Then
MsgBox FromPath & " doesn't exist"
Exit Sub
End If

If FSO.FolderExists(ToPath) = False Then
MsgBox ToPath & " doesn't exist"
Exit Sub
End If

FSO.CopyFile Source:=FromPath & FileExt, Destination:=ToPath
MsgBox "Files moved from " & FromPath & " to " & ToPath


End Sub

Put date stamp on file name using VBA
 

VBA has the Name statement
Name OldFileName As NewFileName

And you'll need some function like
Function AddDateToFilename(OldFileName As String, _
Optional WhichDate As Variant, _
Optional FormatStr As String = "mmddyyyy") _
As String
Dim ExtensionSep As Long
Dim FormattedDate As String

ExtensionSep = InStrRev(OldFileName, ".")
If IsMissing(WhichDate) = True Then WhichDate = Date
FormattedDate = Format(WhichDate, FormatStr)

If ExtensionSep 0 Then
AddDateToFilename = Left(OldFileName, ExtensionSep - 1) & FormattedDate
& Right(OldFileName, Len(OldFileName) - ExtensionSep + 1)
Else
AddDateToFilename = OldFileName & FormattedDate
End If

End Function

NickHK

"CAM" wrote in message
...
Hello,

I desperately need help on this one. Below I have a command button coded
in
Excel to copy files from the source folder to the destination folder,
which
works fine. This coding is from Ron de Bruin website, which is an
excellent
resource. My problem is I want to copy the files from the source folder
into the destination folder, but put a date stamp at the end of the file
name. For example "claim.xls" from the source folder and I want it to
change it to "claim01032007.xls. I want the date stamp done in every file
copied to the destination folder. Is this possible? I have many files
from
the source folder. Any tips or website to visit will be appreciated.
Thank
you in advance.


Private Sub cmdMoveFiles_Click()

Dim FSO As Object
Dim FromPath As String
Dim ToPath As String
Dim FileExt As String

FromPath = "D:\Test" 'From Path Folder
ToPath = "D:\Audits\" 'To Path - Destination Folder
FileExt = "*.xls"


If Right(FromPath, 1) < "\" Then
FromPath = FromPath & "\"
End If

Set FSO = CreateObject("scripting.filesystemobject")

If FSO.FolderExists(FromPath) = False Then
MsgBox FromPath & " doesn't exist"
Exit Sub
End If

If FSO.FolderExists(ToPath) = False Then
MsgBox ToPath & " doesn't exist"
Exit Sub
End If

FSO.CopyFile Source:=FromPath & FileExt, Destination:=ToPath
MsgBox "Files moved from " & FromPath & " to " & ToPath


End Sub

Put date stamp on file name using VBA
 

Hey Nick,

Thanks for the fast response. A couple of questions, where do I put this
within my code or is this a separate statement? Does it use the system
date? Thanks again.

Regards,

"NickHK" wrote in message
...
VBA has the Name statement
Name OldFileName As NewFileName

And you'll need some function like
Function AddDateToFilename(OldFileName As String, _
Optional WhichDate As Variant, _
Optional FormatStr As String = "mmddyyyy") _
As String
Dim ExtensionSep As Long
Dim FormattedDate As String

ExtensionSep = InStrRev(OldFileName, ".")
If IsMissing(WhichDate) = True Then WhichDate = Date
FormattedDate = Format(WhichDate, FormatStr)

If ExtensionSep 0 Then
AddDateToFilename = Left(OldFileName, ExtensionSep - 1) & FormattedDate
& Right(OldFileName, Len(OldFileName) - ExtensionSep + 1)
Else
AddDateToFilename = OldFileName & FormattedDate
End If

End Function

NickHK

"CAM" wrote in message
...
Hello,

I desperately need help on this one. Below I have a command button coded
in
Excel to copy files from the source folder to the destination folder,
which
works fine. This coding is from Ron de Bruin website, which is an
excellent
resource. My problem is I want to copy the files from the source folder
into the destination folder, but put a date stamp at the end of the file
name. For example "claim.xls" from the source folder and I want it to
change it to "claim01032007.xls. I want the date stamp done in every file
copied to the destination folder. Is this possible? I have many files
from
the source folder. Any tips or website to visit will be appreciated.
Thank
you in advance.


Private Sub cmdMoveFiles_Click()

Dim FSO As Object
Dim FromPath As String
Dim ToPath As String
Dim FileExt As String

FromPath = "D:\Test" 'From Path Folder
ToPath = "D:\Audits\" 'To Path - Destination Folder
FileExt = "*.xls"


If Right(FromPath, 1) < "\" Then
FromPath = FromPath & "\"
End If

Set FSO = CreateObject("scripting.filesystemobject")

If FSO.FolderExists(FromPath) = False Then
MsgBox FromPath & " doesn't exist"
Exit Sub
End If

If FSO.FolderExists(ToPath) = False Then
MsgBox ToPath & " doesn't exist"
Exit Sub
End If

FSO.CopyFile Source:=FromPath & FileExt, Destination:=ToPath
MsgBox "Files moved from " & FromPath & " to " & ToPath


End Sub

Put date stamp on file name using VBA
 

I don't use the FSO, so I can't say if you can specify a new name in the
..FileCopy call.
If not, at the end of you code add

Name FromPath & FileExt As AddDateToFilename(FromPath & FileExt)

As for the system, if you do not pass a date in the WhichDate argument, it
will use the date as retuned by the Date function, the system date.

NickHK

"CAM" wrote in message
...
Hey Nick,

Thanks for the fast response. A couple of questions, where do I put this
within my code or is this a separate statement? Does it use the system
date? Thanks again.

Regards,

"NickHK" wrote in message
...
VBA has the Name statement
Name OldFileName As NewFileName

And you'll need some function like
Function AddDateToFilename(OldFileName As String, _
Optional WhichDate As Variant, _
Optional FormatStr As String = "mmddyyyy") _
As String
Dim ExtensionSep As Long
Dim FormattedDate As String

ExtensionSep = InStrRev(OldFileName, ".")
If IsMissing(WhichDate) = True Then WhichDate = Date
FormattedDate = Format(WhichDate, FormatStr)

If ExtensionSep 0 Then
AddDateToFilename = Left(OldFileName, ExtensionSep - 1) &
FormattedDate
& Right(OldFileName, Len(OldFileName) - ExtensionSep + 1)
Else
AddDateToFilename = OldFileName & FormattedDate
End If

End Function

NickHK

"CAM" wrote in message
...
Hello,

I desperately need help on this one. Below I have a command button
coded
in
Excel to copy files from the source folder to the destination folder,
which
works fine. This coding is from Ron de Bruin website, which is an
excellent
resource. My problem is I want to copy the files from the source
folder
into the destination folder, but put a date stamp at the end of the
file
name. For example "claim.xls" from the source folder and I want it to
change it to "claim01032007.xls. I want the date stamp done in every
file
copied to the destination folder. Is this possible? I have many files
from
the source folder. Any tips or website to visit will be appreciated.
Thank
you in advance.


Private Sub cmdMoveFiles_Click()

Dim FSO As Object
Dim FromPath As String
Dim ToPath As String
Dim FileExt As String

FromPath = "D:\Test" 'From Path Folder
ToPath = "D:\Audits\" 'To Path - Destination Folder
FileExt = "*.xls"


If Right(FromPath, 1) < "\" Then
FromPath = FromPath & "\"
End If

Set FSO = CreateObject("scripting.filesystemobject")

If FSO.FolderExists(FromPath) = False Then
MsgBox FromPath & " doesn't exist"
Exit Sub
End If

If FSO.FolderExists(ToPath) = False Then
MsgBox ToPath & " doesn't exist"
Exit Sub
End If

FSO.CopyFile Source:=FromPath & FileExt, Destination:=ToPath
MsgBox "Files moved from " & FromPath & " to " & ToPath


End Sub

Put date stamp on file name using VBA
 

Thanks again Nick.

Regards,


"NickHK" wrote in message
...
I don't use the FSO, so I can't say if you can specify a new name in the
.FileCopy call.
If not, at the end of you code add

Name FromPath & FileExt As AddDateToFilename(FromPath & FileExt)

As for the system, if you do not pass a date in the WhichDate argument, it
will use the date as retuned by the Date function, the system date.

NickHK

"CAM" wrote in message
...
Hey Nick,

Thanks for the fast response. A couple of questions, where do I put this
within my code or is this a separate statement? Does it use the system
date? Thanks again.

Regards,

"NickHK" wrote in message
...
VBA has the Name statement
Name OldFileName As NewFileName

And you'll need some function like
Function AddDateToFilename(OldFileName As String, _
Optional WhichDate As Variant, _
Optional FormatStr As String = "mmddyyyy") _
As String
Dim ExtensionSep As Long
Dim FormattedDate As String

ExtensionSep = InStrRev(OldFileName, ".")
If IsMissing(WhichDate) = True Then WhichDate = Date
FormattedDate = Format(WhichDate, FormatStr)

If ExtensionSep 0 Then
AddDateToFilename = Left(OldFileName, ExtensionSep - 1) &
FormattedDate
& Right(OldFileName, Len(OldFileName) - ExtensionSep + 1)
Else
AddDateToFilename = OldFileName & FormattedDate
End If

End Function

NickHK

"CAM" wrote in message
...
Hello,

I desperately need help on this one. Below I have a command button
coded
in
Excel to copy files from the source folder to the destination folder,
which
works fine. This coding is from Ron de Bruin website, which is an
excellent
resource. My problem is I want to copy the files from the source
folder
into the destination folder, but put a date stamp at the end of the
file
name. For example "claim.xls" from the source folder and I want it
to
change it to "claim01032007.xls. I want the date stamp done in every
file
copied to the destination folder. Is this possible? I have many
files
from
the source folder. Any tips or website to visit will be appreciated.
Thank
you in advance.


Private Sub cmdMoveFiles_Click()

Dim FSO As Object
Dim FromPath As String
Dim ToPath As String
Dim FileExt As String

FromPath = "D:\Test" 'From Path Folder
ToPath = "D:\Audits\" 'To Path - Destination Folder
FileExt = "*.xls"


If Right(FromPath, 1) < "\" Then
FromPath = FromPath & "\"
End If

Set FSO = CreateObject("scripting.filesystemobject")

If FSO.FolderExists(FromPath) = False Then
MsgBox FromPath & " doesn't exist"
Exit Sub
End If

If FSO.FolderExists(ToPath) = False Then
MsgBox ToPath & " doesn't exist"
Exit Sub
End If

FSO.CopyFile Source:=FromPath & FileExt, Destination:=ToPath
MsgBox "Files moved from " & FromPath & " to " & ToPath


End Sub