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]

@Joe4 It appears to have been another snippet within the code causing the proposed solutions to not work. I've corrected that, and it works fine for the particular file I was working with. I tried to use the same code to ingest another file. It's not working for the new file, so can you explain your code? I assumed that I could adjust the digits after s.name, but that doesn't seem to work. Instead of you providing the right code, I'd prefer to understand it so I can use it in the future.

OK, you said that the line:

MsgBox s.Name

is returning "Rob-Tom Work 01.06.23.xlsx".

From that, you want to return "01/06/23".

So, the first thing we do is to get the last 13 characters of the string, which we can do with:

Right(s.Name, 13)

which will chop off the stuff in front and we will be left with:
"01.06.23.xlsx"

Now, we want to chop off the extension at the end, so we can just take the first 8 characters with:

Left(Right(s.Name, 13), 8)

which leaves us with "01.06.23".

Almost there, but you want "/" instead of "." to separate the parts of the date.
So, we just need to replace all "." with "/" like this:

Replace(Left(Right(s.Name, 13), 8), ".", "/")

to get our desired result of "01/06/23".

We just keep building out like that until we get what we want.
Make sense?

 

[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), ".", "/")