Rename Files Using VBA 1

[kitackers]

I hope someone can help

i have a problem in that there is a folder created auitomatically everynight with a number of txt files, what i need to be able to do is rename these files using VBA.

I have the following code that i thought would work

Dim oApp As Object
Dim FilePath As String, Draft As String, FinalFilePath As String


FilePath = "H:\Retail Network\Private\GMS Call Centre\Call Centre\MiDatabases\BranchTelephony\Work In Progress\OutboundData\"

Draft = "Branch_report" & "*"

FinalFilePath = FilePath & Draft

If Dir(FilePath & Draft, vbNormal) <> "" Then
Name FinalFilePath As "H:\Retail Network\Private\GMS Call Centre\Call Centre\MiDatabases\BranchTelephony\Work In Progress\OutboundData\BranchReport230408.txt"
Else
MsgBox "The file selected Does Not Exist", vbExclamation, "File Does Not Exist"
End If

however i have since realised that you can not use wildcard with the name function and was hoping someone could help me with the best way to achieve theis

 

[Golom]

Dim oApp As Object
Dim FilePath As String, Draft As String, FinalFilePath As String

Dim FileName As String
Dim DestPath As String

FilePath = "H:\Retail Network\Private\GMS Call Centre\Call Centre\MiDatabases\BranchTelephony\Work In Progress\OutboundData\"

DestPath = "H:\Retail Network\Private\GMS Call Centre\Call Centre\MiDatabases\BranchTelephony\Work In Progress\OutboundData\"

Draft = "Branch_report" & "*"

FinalFilePath = FilePath & Draft

FileName = Dir(FilePath & Draft, vbNormal)

If FileName <> "" Then
  
Name FilePath & FileName As DestPath & "BranchReport230408.txt"

Else

MsgBox "No Files Found"

End If

Note however that this will rename only the first such file. You would need to process in a Do Loop to rename all of them.

 

[kitackers]

golom

thanks you very much for you help with this, it works fine.. i am just adapting the code slightly so i can use it with another file and was wondering if it is possible to not only look for a matching string like branch_name but alos to look for any file for the previous day so it looks like branch_name- 6-18-08

thanks again

 

[Golom]

I suppose you could

LookFor = "branch_name-" & Format(Date()-1,"mm-dd-yy")

But I'm unclear if you want to look for files with that format and then rename them

OR

you want to use that as the new name for the file.

 

[kitackers]

sory i want to look for files with that dat format amd then rename them

sorry if i was unclear

 

[Golom]

Looks like a simple extension of what you are now doing

Draft = "Branch_report" & Format(Date()-1,"mm-dd-yy") & "*"

FinalFilePath = FilePath & Draft

FileName = Dir(FilePath & Draft, vbNormal)

If FileName <> "" Then
[COLOR=black cyan]' The issue is here. How do you want to[/color]
[COLOR=black cyan]' structure the new name?[/color]
Name FilePath & FileName As DestPath & "BranchReport230408.txt"

Else

MsgBox "No Files Found"

End If

 

[kitackers]

the structure of the files is currently Branch_name - mm/dd/yy

 

[PHV]

the structure of the files is currently Branch_name - mm/dd/yy
I highly doubt that you have file names with embedded slash (/) ...