Changing FilePath from user Select to a "fix" FilePath

[TheHack22]

Imran Isshack​	12:24 PM (30 minutes ago)
to me

Hi All,

I have this VBA code I found on a MrExcel Thread which is working well, except I must manually select the target folder each time it’s executed.
This VBA renames all files in a folder based on a cell value(A2). Is there a way I can amend this so that it selects my intended folder automatically? I would like my filePath to be:

filepath = "C:\Users\C-TL\Box\Amp_Views\DataDownLoads\"


Sub RenameAllExcelFilesInDirectory()

With Application.FileDialog(msoFileDialogFolderPicker)
.Show
filepath = .SelectedItems(1)
End With

Set r = Workbooks.Add.Worksheets(1).Range("A2")
StrFile = Dir(filepath & "\*.*")

Do While Len(StrFile) > 0
strExtension = Split(StrFile, ".")(UBound(Split(StrFile, ".")))

Set WB = Workbooks.Open(filepath & "\" & StrFile)
StrNewfullfilename = WB.Sheets(1).Range("A2").Value & "." & strExtension
WB.Close

r.Value = StrFile 'print old name
r.Offset(, 1).Value = StrNewfullfilename 'print new name
Set r = r.Offset(1)
Name filepath & "\" & StrFile As filepath & "\" & StrNewfullfilename
StrFile = Dir
Loop
End Sub

 

[johnnyL]

Replace:

With Application.FileDialog(msoFileDialogFolderPicker)
.Show
filepath = .SelectedItems(1)
End With

with:

filepath = "C:\Users\C-TL\Box\Amp_Views\DataDownLoads"

 

[TheHack22]

Replace:

With Application.FileDialog(msoFileDialogFolderPicker)
.Show
filepath = .SelectedItems(1)
End With

with:

filepath = "C:\Users\C-TL\Box\Amp_Views\DataDownLoads"

@jonnyl

Thanks very much for your response. I tried that and now it's breaking at this point:
Name filepath & "\" & StrFile As filepath & "\" & StrNewfullfilename

Error message run time error 5 "Invalid procedure call or argument"

Imran