VBA issue with renaming a file

[whirlwind147]

Hi,
I have very limited experience of VBA so I'm copying and pasting things together from all over the place to do the jobs I need excel to do. It's worked fine so far but I've got an issue with my current macro. I'm trying to rename a file but I keep getting errors. I just know that all of you who view this will spot the error immediately but I've tried a few things but none have worked. Here is what I have:

Private Sub CommandButton2_Click()
Sub RenameFile()
Dim src As String, dst As String, fl As String
Dim rfl As String
'Folder
src = Range("B111")
'File name
fl = Range("B108")
'Rename file
rfl = Range("B110")
On Error Resume Next
    Name src & "\" & fl As src & "\" & rfl
    If Err.Number <> 0 Then
        MsgBox "Error: " & src & "\" & rfl
    End If
On Error GoTo 0
End Sub

Any help will be greatly appreciated!

[GTO]

At first blush, what is this?

Name src & "\" & fl As src & "\" & rfl

There should not be an 'As' in there. 'As' is for typing a variable (Dim s As String).

Mark

[whirlwind147]

Hi Mark,

I don't know what the 'As' but I copied the most part from this website w w w .get-digital-help.com/2013/04/19/copyrename-a-file-excel-vba/
If I remove the 'As' the whole line of the code goes red and it still doesn't work.
(I had to separate the www otherwise it wouldn't let me post!)

[westconn1]

what error are you getting?
what are the contents of the 3 cells?

[westconn1]

There should not be an 'As' in there.

as is correct for name statement

[whirlwind147]

B111 = W:\COSTING (specs for)
B108 = Hello - 12 Facing FSU.xls
B110 = 25673 Hello.xls
(B108 & B110 are formulae made up from other cells)

I have since discovered that if I removed the sub renamefile line it still didn't work. I get Error: W:\COSTING (specs for)\28154 Hello.xls

However, if I manually save the file as something else, on this occasion test.xls and then I clicked the button it worked fine. This will cause an issue though as this button will be used within the original file not when it's been manually saved as something else.

Is there any mileage in saving a copy of the file as the new file name and writing something in to the end of the code to delete the old one? The only issue with this is I don't know how to do this :-/

[westconn1]

as the code prevents a proper error message it is hard to know what error is occurring
if you remove or comment out on error resume next, you may get a more meaningful error message, including error number and description

you will get an error if the file is in a different location, the destination file already exists and i am sure many other possible reasons, but it would be good to know which is causing the problem

[whirlwind147]

This has been sorted now, I've gone down the save a copy and kill the original file and it all works fine now, thanks to all for your help.

[GTO]

as is correct for name statement

My bad. Thanks for the catch westconn :-)

Mark