CODEWITHSUNDEEP
ms-accessvba
Jim
Jim

Reputation: 23

Access VBA - Rename File With Periods In The Name

So, I'm attempting to rename a file (Enrollment tracking 07.30.xlsx) to "Enrollment tracking.xlsx", but it hates the period in the date, and it won't do it. 

    Path = "V:\CORPDATA17\MBRSHIPANDBILL\Alegeus Migration Tracker\Daily Reports\"
    OriginalName = Dir("V:\CORPDATA17\MBRSHIPANDBILL\Alegeus Migration Tracker\Daily Reports\*Enrollment tracking*")
    NewName = "V:\CORPDATA17\MBRSHIPANDBILL\Alegeus Migration Tracker\Daily Reports\Enrollment tracking.xlsx"
    Name Path & OriginalName As NewName

It will toss the runtime error 75: "Path/File access error"...

Is there any way to do this? I can get it to identify the file name with the "OriginalName" variable (it will show as "Enrollment tracking 07.30.xlsx"), but I can't get it to rename the file.

Upvotes: 0

Views: 1163

Answers (1)

Blenm
Blenm

Reputation: 86

The periods aren't the issue, its the path.

Take a closer look at your last line:

Name Path & OriginalName As NewName

The issue is that you already put the path in the variable OriginalName, as shown below:

Path = "V:\CORPDATA17\MBRSHIPANDBILL\Alegeus Migration Tracker\Daily Reports\"
OriginalName = Dir("V:\CORPDATA17\MBRSHIPANDBILL\Alegeus Migration Tracker\Daily Reports\*Enrollment tracking*")

so, you're trying to rename the file under Path & Originalname, which would be:

"V:\CORPDATA17\MBRSHIPANDBILL\Alegeus Migration Tracker\Daily Reports\V:\
CORPDATA17\MBRSHIPANDBILL\Alegeus Migration Tracker\Daily Reports\
*Enrollment tracking*"

Which would understandibly cause an error.

Try replacing your last line with simply:

Name OriginalName As NewName

I tried this approach on my computer and it worked, here's my full code:

Sub changeFileName()
    origFile = "C:\Users\name\Desktop\1.2.3.txt"
    NewFile = "C:\Users\name\Desktop\123.txt"
    Name origFile As NewFile
End Sub

EDIT: you can actually use the Dir() function, when I tested the code above I forgot to include it. This code also worked for me, so maybe there's an error in file/path name? that's what the error seems to indicate. See code:

Sub changeFileName2()
    Path = "C:\Users\name\Desktop\"
    origFile = Dir("C:\Users\name\Desktop\1.2.3.txt")
    NewFile = "C:\Users\name\Desktop\123.txt"
    Name Path & origFile As NewFile
End Sub

Upvotes: 1

Related Questions