CODEWITHSUNDEEP
vbaexcel
aLearningLady
aLearningLady

Reputation: 2088

VBA: Determining Date Based on Workbook Name

I have a worksheet that gets sent out every month with the name MONTH YEAR Report (e.g. January 2016 Report)

The report has all the necessary information within the worksheet with the exception of the month and year. Is there a way to pull the month and year based on the name of the workbook to a column?

Upvotes: 0

Views: 593

Answers (3)

Grade 'Eh' Bacon
Grade 'Eh' Bacon

Reputation: 3823

You can have this formula put into the workbook which gets carried forward monthly:

=MID(CELL("filename",B1),FIND("[",CELL("filename",B1))+1,FIND(".xls",CELL("filename",B1))-FIND("[",CELL("filename",B1))-1)

This looks at the workbook's own filename, and pulls every character in between the "[" & "]" brackets. That is, it doesn't include the filepath and sheet name.

Upvotes: 1

Tom
Tom

Reputation: 9878

Another way to achieve this irregardless of the name structure

Sub GetMonthAndYear()
    Dim i As Integer, yr As Integer
    Dim na As String, mnth As String
    Dim arr

    na = ThisWorkbook.Name

    For i = 1 To 12
        If InStr(1, na, MonthName(i, True)) > 0 Then
            mnth = MonthName(i)
            Exit For
        End If
    Next i

    arr = Split(na, " ")

    On Error Resume Next
    yr = Join(Filter(arr, 19), "")
    yr = Join(Filter(arr, 20), "")
    On Error GoTo 0

    If yr = 0 Then
        MsgBox "Can't find year of report for " & na
    Else
        MsgBox "Month of report is: " & mnth & vbNewLine & "Year of report is: " & yr
    End If
End Sub

Upvotes: 1

Stupid_Intern
Stupid_Intern

Reputation: 3450

You can try this to get the month and year from the given filename if the format of the filename is maintained.

 Sub MonAndYear()
    Dim MyNAme As String
    Dim Cet 
    MyName = ThisWorkbook.Name
    Cet =Split(MyName," ")
    Dim Mon As String
    Dim Yr As String
    Mon = Cet(0)
    Yr = Cet(1)

     MsgBox "Month is " & Cet(0)
     MsgBox "Year is " & Cet(1)


End Sub

Upvotes: 2

Related Questions