1

I am trying to rename a dynamic file within VBA code. The structure of this dynamic file is as below, and to what it should be renamed to

enter image description here

I have searched many places to look for renaming in VBA code for excel but couldn't find.

I tried File copy method and using wildcard and it errors out for the following code :

enter image description here

FileCopy "\C:\Users\User12345\Desktop\ReportFolder*.csv*", "C:\Users\User12345\Desktop\ReportFolder\Daily_Report.csv"

RunTime Error 52 : Bad Filename or Number

Any help is appreciated. Thanks.

MRBasun
  • 83
  • 6
  • You are renaming to the same file name each file? – Nathan_Sav Apr 21 '22 at 16:01
  • You can't use `*` in `FileCopy`. Try using `Dir` first maybe. – BigBen Apr 21 '22 at 16:01
  • BigBen, I am new to this, can you please explain how this is done. Thank you so much. – MRBasun Apr 21 '22 at 16:02
  • There are many questions on SO dealing with `Dir`. Also there is the [documentation](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/dir-function). – BigBen Apr 21 '22 at 16:03
  • Nathan_Sav - Yes , always renaming to same file. There is only 1 dynamic file inside a folder for a given day, and I need to convert that to Daily_Report.csv – MRBasun Apr 21 '22 at 16:03

1 Answers1

0

If you just have only one file in the directory so let the code detect the file name and then rename it as you wish. Try the following code

Sub Test()
    Dim sFolder As String, sFile As String
    sFolder = ThisWorkbook.Path & "\TestFolder\"
    sFile = Dir(sFolder & "*csv")
    Do While Len(sFile) > 0
        Name sFolder & sFile As sFolder & "Daily_Report.csv"
        Exit Do
    Loop
End Sub
YasserKhalil
  • 9,138
  • 7
  • 36
  • 95
  • 1
    Maybe change the `.xlsx` to `.csv`? Also, if there's only one file to be changed in the directory, there's no need for a `Do While` loop. – BigBen Apr 21 '22 at 16:34