Partial Filename Extract Not Returning Expected Results

[reberryjr]

I have some files that have data I'm importing into a master file. These files have variable names, with the last 8 characters being a date (e.g., 01.06.23). When I try to extract the date and put it in a range of cells, I'm getting a completely different date (12/30/1999). What adjustments to the code do I need to make so that I can populate the range with 01.06.23? Note...Ideally, the date would come over as 01/06/23. Here's what I've tried so far:

'mD.Range("H" & mDNR & ":H" & mDNLR).Value = Format(Mid$(s.Name, 14, 8), "MM/DD/YY")
'mD.Range("H" & mDNR & ":H" & mDNLR).Value = Format(Mid$(s.Name, 14, 8), "@")
mD.Range("H" & mDNR & ":H" & mDNLR).Value = Mid$(s.Name, 14, 8)

Dim m As Workbook, s As Workbook
Dim mD As Worksheet, sD As Worksheet
Dim FP As String, FN As String
Dim mDLR As Long, mDNR As Long, mDNLR As Long, sDLR As Long

Set m = ThisWorkbook
Set mD = m.Sheets("Data")

'Sets the Last Row and Next Row on the Data tab.
mDLR = mD.Range("A" & Rows.Count).End(xlUp).Row
mDNR = mD.Range("A" & Rows.Count).End(xlUp).Row + 1

'Opens the target Source File.

FP = "Blah Blah\"
FN = "Rob-Tom Work"
FN = Dir(FP & FN & "*.xlsx")

Set s = Workbooks.Open(FP & FN)

Set sD = s.Sheets("Accounts")

sD.Activate

'Removes filters if any exist.
If sD.AutoFilterMode Then sD.AutoFilterMode = False

'Sets the Last Row on the Source File.
sDLR = sD.Range("A" & Rows.Count).End(xlUp).Row

mDNLR = mD.Range("E" & Rows.Count).End(xlUp).Row
    
'Copies the File Date and pastes it into the Data tab.
'mD.Range("H" & mDNR & ":H" & mDNLR).Value = Format(Mid$(s.Name, 14, 8), "MM/DD/YY")
'mD.Range("H" & mDNR & ":H" & mDNLR).Value = Format(Mid$(s.Name, 14, 8), "@")
mD.Range("H" & mDNR & ":H" & mDNLR).Value = Mid$(s.Name, 14, 8)

 

[Joe4]

Please post a few examples of the FULL file names, so we can see exactly what you are working with.

 

[Joe4]

Or, it might even be more to add a new line of code after this line:

Set s = Workbooks.Open(FP & FN)

that looks like this:

MsgBox s.Name

and tell us what it returns.

That will help ensure that the previous part of the code is working, and show us what it thinks that value is at that point in time.

 

[reberryjr]

Or, it might even be more to add a new line of code after this line:

Set s = Workbooks.Open(FP & FN)

that looks like this:

MsgBox s.Name

and tell us what it returns.

That will help ensure that the previous part of the code is working, and show us what it thinks that value is at that point in time.

@Joe4 The msgbox returned the true file name of Rob-Tom Work 01.06.23.xlsx

 

[Joe4]

Try this for your calculation:

mD.Range("H" & mDNR & ":H" & mDNLR).Value = Replace(Left(Right(s, 13), 8), ".", "/")

 

[reberryjr]

Try this for your calculation:

mD.Range("H" & mDNR & ":H" & mDNLR).Value = Replace(Left(Right(s, 13), 8), ".", "/")

I got the Object doesn't support this property or method error at the line you gave me.

 

[Joe4]

Whoops, it should be "s.Name", not just "s":

mD.Range("H" & mDNR & ":H" & mDNLR).Value = Replace(Left(Right(s.Name, 13), 8), ".", "/")

 

[reberryjr]

Whoops, it should be "s.Name", not just "s":

mD.Range("H" & mDNR & ":H" & mDNLR).Value = Replace(Left(Right(s.Name, 13), 8), ".", "/")

No change. I'm still getting the 12/30/1999 date.

 

[Joe4]

Just for kicks, temporarily change it to this and see what you get:

mD.Range("H" & mDNR & ":H" & mDNLR).Value = "Date is " & Replace(Left(Right(s.Name, 13), 8), ".", "/")

 

[reberryjr]

Just for kicks, temporarily change it to this and see what you get:

mD.Range("H" & mDNR & ":H" & mDNLR).Value = "Date is " & Replace(Left(Right(s.Name, 13), 8), ".", "/")

Literally just got the 12/30/1999. There was no "Date Is" in cells. LOL