CODEWITHSUNDEEP
vbaexcelfilesystems
Rhyfelwr
Rhyfelwr

Reputation: 329

Trying to identify and rename a file with an original name that contains a random number

Hi there I am trying to rename a file in a folder (with multiple similar files), but I have a hard time identifying it even with the wildcard method. The original file name looks like this: "2018_02_26_20180228_XXXXXX_GDW_Audit_CView_Report.txt" so my only way of identifying it is knowing parts of the name ("_GDW_Audit_CView_Report.txt") as the date is the same across multiple files. I wrote the following code which gives Path/File Access error when I try to run it. Any help would be appreciated.

Option Explicit
Sub HGDW_WKD()

    Dim myDateTemp As String
    Dim myDate1 As String
    Dim myDate2 As String
    Dim HGDW_CV1 As String
    Dim HGDW_CV2 As String

    myDateTemp = Format(Date, "yyyy-mm-dd")
    myDate1 = Replace(myDateTemp, "-", "_")
    myDate2 = Format(Date, "yyyymmdd")

    HGDW_CV1 = myDate1 & "_" & myDate2 & "*_GDW_Audit_CView_Report.txt*"
    HGDW_CV2 = "35999_HR_Global_Data_Warehouse_CView_PROD_" & myDate2 & ".txt"

    Name "C:\Users\bf91955\SourceFldr\" & HGDW_CV1 As _ 
    "C:\Users\bf91955\SourceFldr\" & HGDW_CV2

End Sub

Upvotes: 0

Views: 344

Answers (1)

QHarr
QHarr

Reputation: 84465

I am not sure if this is what you mean but you can use the Dir function with wildcards to try and get the filename.

So say I had a file called

2018_02_28_20180228_XXXXXX_GDW_Audit_CView_Report.txt

I can retrieve the actual name as follows. First match. No real error handling if not found just test that string is assigned a value other than that which it was initialized with. You can pass the folder path as a variable.

Sub TEST()

    Dim fname As String

    fname = Dir("C:\Users\User\Desktop\Test\*_GDW_Audit_CView_Report.txt")

    If fname <> vbNullString Then

        Debug.Print fname

    End If

End Sub

Example passing a folder variable and date1 (note date1 = "2018_02_28" as of this moment) :

Sub TEST()

    Dim myDate1 As String

    myDate1 = Format$(Date, "yyyy_mm_dd")

    'Debug.Print myDate1

    Dim fname As String
    Dim folderPath As String

    folderPath = "C:\Users\User\Desktop\Test"

    fname = Dir(folderPath & Application.PathSeparator & myDate1 & "*_GDW_Audit_CView_Report.txt")

    If fname <> vbNullString Then

        Debug.Print fname

    End If

End Sub

And why did I use Application.PathSeparator ? You should really test for the presence of a separator before trying to add one. In this case I used it for compatibility reasons. This will use the correct separator across Mac and Windows.

For info see here:

Excel 2016 Power Programming with VBA (2016) Part IV. Developing Excel Applications , Chapter 21. Understanding Compatibility Issues

Quote:

If your code deals with paths and filenames, you need to construct your path with the appropriate path separator (a colon for the Mac, a backslash for Windows). A better approach is to avoid hard-coding the path separator character and use VBA to determine it. The following statement assigns the path separator character to a variable named PathSep:

PathSep = Application.PathSeparator

Reference:

http://www.excelfunctions.net/vba-dir-function.html

Upvotes: 3

Related Questions