Move file with name causes error 5, invalid procedure call or argument

Question

It seems I have some problems with the hard drive itself. It's acting strange. Some files appear duplicates and I delete the duplicates. A few minutes later i refresh the directory and they are back. Refresh again and they are gone. Files are visible on one computer but not the other. I will try to trouble shoot it and see where what the issue is. Thanks for all the help so far it may be more than one problem acting together.

I have used this code to rename and move files from one location to another on my network drive.

The video files are named 00001, 00002 etc.. and since the counter resets i need to rename the files to something that can work on a harddrive.

So all files are renamed to <date> <time>.MTS

This code used to work but now it just stoped working by some reason.

Sub MoveFiles()
    Dim r As Integer
    r = 2 'first line of files
    Set objFSO = CreateObject("Scripting.FileSystemObject")

    Do Until IsEmpty(Cells(r, "A")) Or IsEmpty(Cells(r, "B"))
        dirPath = Cells(r, "C") + "\" + Cells(r, "B")
        If objFSO.FileExists(dirPath) Then
            ' file exist output error message
            MsgBox ("Filen finns redan!!! " + Cells(r, "A") + "   " + Cells(r, "B"))
        Else
            FromName = ActiveWorkbook.Path + "\" + Cells(r, "A")
            ToName = Cells(r, "C") + "\" + Cells(r, "B")

            ' none of the methods below work.
            Name FromName As ToName
            Name ActiveWorkbook.Path + "\" + Cells(r, "A") As Cells(r, "C") + "\" + Cells(r, "B")
        End If
        r = r + 1
    Loop



End Sub

Since the code does not create the error message that the ToName exsists then it's not a "duplicate" issue.
If I run the following code

If objFSO.FileExists(ActiveWorkbook.Path + "\" + Cells(r, "A")) Then
    MsgBox "test"
End If

I get the message box, which means that FromName file exsists.

So in short the file exsists and the filename that it will become does not exsist. Also the paths (directories) exsist as they are created in a earlier sub(). And I have double checked it. So what can be the problem?
I'm completly lost here.

Edit; Workbook picture added:

Answer 1

This should do it (not tested — YMMV):

Option Explicit

Sub MoveFiles()
    Dim rownum As Long
    rownum = 2 'first line of files
    Dim objFSO As Object        ' Required because of Option Explicit
    Set objFSO = CreateObject("Scripting.FileSystemObject")

    Dim FromName as String
    Dim ToName as String

    Do Until IsEmpty(Cells(rownum, "A")) Or IsEmpty(Cells(rownum, "B")) or rownum > 1048576
        ToName = CStr(Cells(rownum, "C")) + "\" + CStr(Cells(rownum, "B"))
        If objFSO.FileExists(ToName) Then
            ' file exist output error message
            MsgBox ("Filen finns redan!!! " + Cells(rownum, "A") + "   " + Cells(rownum, "B"))
        Else
            FromName = ActiveWorkbook.Path + "\" + CStr(Cells(rownum, "A"))

            ' none of the methods below work.
            '' Name FromName As ToName
            '' Name ActiveWorkbook.Path + "\" + Cells(rownum, "A") As Cells(rownum, "C") + "\" + Cells(rownum, "B")

            objFSO.MoveFile FromName, ToName

        End If
        rownum = rownum + 1
    Loop

End Sub

A few things changed:

• Option Explicit: always use it at the top of every VBA file.
• Never use Integer — use Long instead.
• With Option Explicit, you have to have Dim statements for every variable. This prevents you from accidentally creating a new variable because of a typo in a variable's name.
• in the Do loop, include a sanity check on rownum so you don't try to access a nonexistent row.
• Only assign ToName once, and save its value.
• Use FileSystemObject.MoveFile to rename.
• Use CStr() to make sure the value you get from Cells() is a String. That will reduce the risk of unpleasant surprises due to unexpected input data.

This question is not an exact duplicate, but may also have some helpful information.