Timestamp

**preciouslife73** · 02-06-2012, 07:00 PM

I don't understand. IT worked perfectly at home. But when I trying another computer, the first part of the MACRO works GREAT!, it was able to create a Folder with Date Stamp and copy from old path to this new path.

But when it got to the 2nd part where it need to rename the file in the new path it gave me a warning that the file does not exist. How is that possible? It was created in the first place to begin with. It can't be right. I have Microsoft Excel 2010 while the other computer has 2007. I really dont think that would matter. How do I revise this to make it work? (but it works at my home computer)


'This example move all Excel files from FromPath to ToPath.  Note: It will create the folder ToPath for you with a date stamp

Sub RenamedFiles_To_New_Folder()

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

Dim OldDir As String
Dim i As Long
OldDir = CurDir
Dim fName As String

FromPath = "C:\Users\Juan\Desktop\test\Macrofolder"  '<< Change

ToPath = "C:\Users\Juan\Desktop\test\Macrofolder\" & Format(Date,
"yyyy-mm-dd")  '<< Change only the destination folder

FileExt = "*.xlsx"    '<< Change

'You can use *.* for all files or *.doc for word files

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

FNames = Dir(FromPath & FileExt)

If Len(FNames) = 0 Then
        MsgBox "No files in " & FromPath
        Exit Sub
End If
Set FSO = CreateObject("scripting.filesystemobject")

FSO.CreateFolder (ToPath)
FSO.CopyFile Source:=FromPath & FileExt, Destination:=ToPath
Set FilePath = FSO.GetFolder(ToPath)

ChDir FilePath & "\"
fName = Dir(FilePath & "\" & "*.xlsx")

Do While Len(fName) <> 0
i = i + 1

If Not FSO.FileExists(FilePath & "\" & fName & Format(Date, " mm-dd-yy") & ".xlsx") Then

Name fName As Left(fName, InStr(1, fName, ".", vbTextCompare) - 1) & Format(Date, " mm-dd-yy") & ".xlsx" 'THIS IS WHERE THE PROBLEM IS 
 End If

        fName = Dir
Loop

ChDir OldDir

MsgBox "You can find the files from " & FromPath & " in " & ToPath
End Sub

**smuzoen** · 02-06-2012, 07:23 PM

The code seems fine - is it one particular file causing the problem - does it rename any files in the new directory. It may be a permissions issue as you are working in "C\Users". Just for interests sake add some error handling to see if it is just one file or all files. Add

Do While Len(fname)<>0
On Error Resume Next

and see if any files are copied. THIS IS NOT A SOLUTION. It is just to see if one file is an issue. Try the code outside of "C:\users" - set up a test folder "C:\Test" and put some excel files in here - if it works in the test folder then it may be a permissions issue.
I have tried this code and it works fine so it must be a problem on your end.