I am using the excel macro to rename the files in a specific folder . How to fix the Syntax error in Excel vba

0
votes

'This method renames all the filenames in a folder Sub RenameAllFilenamesInAFolder() Dim intRowCount As Integer Dim intCtr As Integer Dim strFileNameExisting As String Dim strFileNameNew As String Dim strFolder As String

'Set the folder path
strFolder = "C:\Users\rchandramohan\"
 
With Sheet1
    'Find the total rows count in the sheet
    'This will be the last non-blank cell in column A...
    intRowCount = .Cells(.Rows.Count, "A").End(xlUp).Row
     
    'Loop through from the 2nd row (1st row is Heading)
    'till the total rows in the sheet
    For intCtr = 2 To intRowCount
        'Get the existing filename from the cell
        strFileNameExisting = .Range("A" & intCtr)

        'Get the new filename from the cell
        strFileNameNew = .Range("B" & intCtr)
         
        'Rename the file
      ** Name strFolder & strFileNameExisting As strFolder & strFileNameNew **
    Next intCtr
End With
 
'Display an appropriate message, once complete
MsgBox "All files renamed successfully!", _
                    vbInformation, "All files renamed"

End Sub

Hi, I need a help, I am using the above code for renaming the files in a folder, I am getting the syntax error in the line where I have marked ** before and after the particular line, Name strFolder & strFileNameExisting As strFolder & strFileNameNew

I am new to this macro and I could not find it out where the mistake is. Please looking for the solution.

enter image description here

1
excelvba
Put a break point on the line, run the function,and and check the values of strFolder, strFileNameExisting, strFolder, & strFileNameNew. – Nicholas Hunter
What kind of syntax error do you get? For me the code compiles without any sytnax error. Do you have any library loaded which overrides Name? – Storax
I am getting the error window saying File not found with the Debug and other buttons. When I click on 'Debug' button, it shows that particular line with a yellow colour. I dont have any libraries which overrides – Radhika Rishi
What is the exact error message you get? Is strFileNameNew a valid Windows file name? – Tim Williams
It is saying, 'File not found' and yes, strFileNameNew is a valid filename – Radhika Rishi

1 Answers

0
votes

The code runs fine for me in Windows. If you're using Mac, this might be the reason why it produces that error.