Entering Partial File Name in a Cell with VBA

[reberryjr]

I'm struggling to find a way to do this efficiently (or at all to be honest). Essentially, instead of using the snippet in red font, I'd like to grab the last 8 characters (prior to the file extension) of the file name; which are always YYYYMMDD and insert them into a cell in MM/DD/YY format. Thoughts on how I can do this?

Sub GetDailyFiles()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Dim m, s As Workbook
Dim mS, mA, sS, sJ, sD As Worksheet
Dim sDate As String
Dim mSLR, mALR, sDLR As Long

Set m = ThisWorkbook
Set mS = m.Sheets("Summaries")
Set mA = m.Sheets("All_Data")

'Opens file picker and sets the chosen file as "s" for future coding reference.
With Application.FileDialog(3)
    .AllowMultiSelect = False
    If .Show Then
        fullpath = .SelectedItems.Item(1)
        Set s = Workbooks.Open(fullpath)
    End If
    
    'Exits the sub if no file is chosen.
    If s Is Nothing Then Exit Sub
    
    Set sS = s.Sheets("Summary")
    Set sJ = s.Sheets("Job=1")
    
  [COLOR=rgb(226, 80, 65)]  'Adds 20 days to the transaction date from the source file and enters it in the Summaries page of the destination file.
    mS.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = DateAdd("d", 20, sS.Range("B7"))[/COLOR]

 

[rlv01]

I'd like to grab the last 8 characters (prior to the file extension) of the file name; which are always YYYYMMDD

    Dim Last8Chars As String
    Last8Chars = Right(Split(FilePath, ".")(0), 8)

... and insert them into a cell in MM/DD/YY format.

    With Range("A1") 'or whatever cell you choose
        .Value = DateSerial(Left(Last8Chars, 4), Mid(Last8Chars, 5, 2), Right(Last8Chars, 2))
        .NumberFormat = "mm/dd/yy"
    End With

 

[reberryjr]

    Dim Last8Chars As String
    Last8Chars = Right(Split(FilePath, ".")(0), 8)

    With Range("A1") 'or whatever cell you choose
        .Value = DateSerial(Left(Last8Chars, 4), Mid(Last8Chars, 5, 2), Right(Last8Chars, 2))
        .NumberFormat = "mm/dd/yy"
    End With

@rlv01 Thank you for the reply. My apologies, as I didn't notice that a reply had been tendered, and only discovered it today when I tried to solve the same issue. I employed your suggestions, but I'm receiving a Subscript Out of Range error on the line with red font.

Dim sDate, sLast8 As String
Dim mSLR, mALR, sDLR As Long

Set m = ThisWorkbook
Set mS = m.Sheets("Summaries")
Set mA = m.Sheets("All_Data")

'Opens file picker and sets the chosen file as "s" for future coding reference.
With Application.FileDialog(3)
    .AllowMultiSelect = False
    If .Show Then
        fullpath = .SelectedItems.Item(1)
        Set s = Workbooks.Open(fullpath)
    End If
   
    'Exits the sub if no file is chosen.
    If s Is Nothing Then Exit Sub
   
    Set sS = s.Sheets("Summary")
    Set sJ = s.Sheets("Job=1")
   
    'sDate = Mid(Right(s, 13), 1, 8)
   
    'Adds 20 days to the transaction date from the source file and enters it in the Summaries page of the destination file.
    'mS.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = DateAdd("d", 20, sS.Range("B7"))

    s.Activate
    sLast8 = Right(Split(FilePath, ".")(0), 8)
    With mS.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = _
        DateSerial(Left(last8, 4), Mid(last8, 5, 2), Right(last8, 2))
        .NumberFormat = "mm/dd/yy"
    End With

 

[Joe4]

Note that trying to color code when using VBA tags does not work well. You can use the "RICH" code tags in order to show formatted text within your code.
I made that change for you above.

Also note that while it probably isn't what is causing your problems, your variable declarations are not doing what you think they are.
For example, this:

Dim mS, mA, sS, sJ, sD As Worksheet

will only set "sD" to be a "Worksheet". All the other ones will be set to "Variant".

Every variable needs to be explicitly declared, or it will default to variant.
So you would either need to do this:

Dim mS As Worksheet, mA As Worksheet, sS As Worksheet, sJ As Worksheet, sD As Worksheet

or this:

Dim mS As Worksheet
Dim mA As Worksheet
Dim sS As Worksheet
Dim sJ As Worksheet
Dim sD As Worksheet

if you want them all declared as Worksheet variables.

The same goes for your other variable declarations too.

 

[rlv01]

Sorry , should have been FullPath, not FilePath

  sLast8 = Right(Split(FullPath, ".")(0), 8)