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]

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

OK, that means either:
1. uyour code is pdating the wrong place
2. you are looking in the wrong place
3. something else is coming along and overwriting the values that line of code is adding.

You did not post all your code, so we cannot check that for you.
I recommend you step through the code line-by-line, while hovering over the variables in order to see what the values are in at that point in time, and look at your sheet to see if the data you are expecting to be updated actually is updating.

 

[reberryjr]

OK, that means either:
1. uyour code is pdating the wrong place
2. you are looking in the wrong place
3. something else is coming along and overwriting the values that line of code is adding.

You did not post all your code, so we cannot check that for you.
I recommend you step through the code line-by-line, while hovering over the variables in order to see what the values are in at that point in time, and look at your sheet to see if the data you are expecting to be updated actually is updating.

It's probably some other snippet of code. I will review it from that angle and circle back.

 

[Joe4]

Do you have any Worksheet_Change procedures automatocally running on that sheet?

If you cannot figure it out, and are willing to share a sanitized version of the file, I can look at it for you.
You would just need to upload it to a file sharing site and provide us a link to it.

 

[reberryjr]

Do you have any Worksheet_Change procedures automatocally running on that sheet?

If you cannot figure it out, and are willing to share a sanitized version of the file, I can look at it for you.
You would just need to upload it to a file sharing site and provide us a link to it.

@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.

 

[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?

 

[reberryjr]

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 I had to come back to this for another project, and realized I never responded to you. My apologies for that. Yes, it makes sense, and yes, it worked on the new project.