Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Opeing older files in Excel 2007 - file date-stamp amended | Excel Discussion (Misc queries) | |||
product date stamp file save | Excel Discussion (Misc queries) | |||
Save File with Date and Time Stamp | Excel Programming | |||
Save file with time date stamp | Excel Programming | |||
Getting a File Date & Time stamp | Excel Programming |