Extract date from the filename and put it in a column

[Razor_Rob]

HI

I would like to extract the date from the filename to a column BL and I've got a variable lDestLastRow to find the last row.
The file and path gets allocated to a Variant fileNameAndPath

 Dim fileNameAndPath As Variant
    fileNameAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLSX), *.XLSX", Title:="Select File To Be Opened")
    If fileNameAndPath = False Then Exit Sub
    'Workbooks.Open Filename:=fileNameAndPath
    Set wbCopy = Workbooks.Open(Filename:=fileNameAndPath, ReadOnly:=True)
    Set wsCopy = wbCopy.Worksheets(1)

Now I would like to extract the date at the end of the file normally "Text Here 21.09.22.xlsx" or the filename can also be " Text 21.9.2022.xlsx" then put a value in Column BL as -> 21-SEP-22 (dd-mmm-yy)

Thanks in advance

 

[cmowla]

Try this:
(Two examples. The first puts in BL1, the second in BL2 of the Active sheet.)

Sub Test__Extract_Date()
Call Extract_Date("C\Users\You\Desktop\Text Here 21.09.22.xlsx", ActiveSheet.Name, "BL", 1)
Call Extract_Date("C\Users\You\Desktop\Text 21.9.2022.xlsx", ActiveSheet.Name, "BL", 2)
End Sub
Sub Extract_Date(fileNameAndPath As String, sheetName As String, columnLetter As String, rowNumber As Long)
Dim fileName As String, datee As String
fileName = Mid(fileNameAndPath, InStrRev(fileNameAndPath, "\") + 1, Len(fileNameAndPath))
datee = Replace(Mid(fileName, InStrRev(fileName, " ") + 1, InStrRev(fileName, ".") - 1 - (InStrRev(fileName, " ") + 1) + 1), ".", "/")
ThisWorkbook.Worksheets(sheetName).Range(columnLetter & rowNumber).Value = Format(CDate(datee), "dd-mmm-yy")
End Sub

 

[Razor_Rob]

sorry forgot some details .
wsCopy is the workbook that I want to get the date on the file name, which the user selects when opened the path and name gets stored in fileNameAndPath
then I want to populate the Column BL from BL3 down to where there are records (or from the bottom all the way up to BL3)

I dont really need to define the actual sheet name as it can change.

All the VBA code is stored in a separate workbook which opens up different workbooks wbCopy (wsCopy) is the source file wbDest (wsDest) is the Destination file

 

[cmowla]

I'll let someone else continue. (This is more than what I bargained for, and you are still being too vague.)

 

[Razor_Rob]

So if the filename is "Sample File 21.09.22.xlsx"
Which was selected by the user.... which is also set as wsCopy / wbCopy

Then gets pasted into another workbook
wsDest / wbDest below

Column BL
From Row 3 down to the last record ie Row 20

BL
21-SEP-22
21-SEP-22
21-SEP-22
21-SEP-22

 

[johnnyL]

I'll let someone else continue. (This is more than what I bargained for, and you are still being too vague.)

I , also, have lost focus on the objective, but the following code should get the 'date' from files like the 2 examples you provided:

    FileDate = Split(Left(fileNameAndPath, InStrRev(fileNameAndPath, ".") - 1), " ")

 

[cmowla]

I , also, have lost focus on the objective, but the following code should get the 'date' from files like the 2 examples you provided:

    FileDate = Split(Left(fileNameAndPath, InStrRev(fileNameAndPath, ".") - 1), " ")

I love split!

It saves 1 line of code (and a little headache).

Sub Test_Split_Method()
Dim s() As String
Dim fileNameAndPath As String
fileNameAndPath = "C\Users\You\Desktop\Text Here 21.09.22.xlsx"
s = Split(Left(fileNameAndPath, InStrRev(fileNameAndPath, ".") - 1), " ")
Debug.Print Format(CDate(Replace(s(UBound(s)), ".", "/")), "dd-mmm-yy")

fileNameAndPath = "C\Users\You\Desktop\Text 21.9.2022.xlsx"
s = Split(Left(fileNameAndPath, InStrRev(fileNameAndPath, ".") - 1), " ")
Debug.Print Format(CDate(Replace(s(UBound(s)), ".", "/")), "dd-mmm-yy")
End Sub

 

[Razor_Rob]

How would you put the result to the cells ? Im getting type mismatch

Dim s() As String
    Dim fileName As String
    fileName = fileNameAndPath
    s = Split(Left(fileName, InStrRev(fileName, ".") - 1), " ")
    Debug.Print Format(CDate(Replace(s(UBound(s)), ".", "/")), "dd-mmm-yy")
    
    lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Row
    wsDest.Range("BL3" & lDestLastRow).Value = s(Format(CDate(Replace(s(UBound(s)), ".", "/")), "dd-mmm-yy"))

 

[Razor_Rob]

I've got the below code and returns 1/9/2022 instead of the value in the immediate window which is 01-Sep-22

Dim s() As String
    Dim fileName As String
    Dim DateVar As Date
   
    fileName = fileNameAndPath
    s = Split(Left(fileName, InStrRev(fileName, ".") - 1), " ")
    Debug.Print Format(CDate(Replace(s(UBound(s)), ".", "/")), "dd-mmm-yy")
    DateVar = Format(CDate(Replace(s(UBound(s)), ".", "/")), "dd-mmm-yy")
       
    lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Row
    wsDest.Range("BL3", "BL" & lDestLastRow).Value = DateVar

I've added this code below just to convert it to text and the correct format, but I'd like to learn the cleaner easier version

With wsDest
        .Range("BL3:BL" & lDestLastRow).NumberFormat = "@"     'format range as text
       
        For Each cell2 In .Range("BL3:BL" & lDestLastRow)
             cell2.Value = Format(cell2 * 1, "dd-mmm-yy")      'Convert each cell
        Next cell2
    End With