CODEWITHSUNDEEP
excelvbaworksheet
mariestr
mariestr

Reputation: 33

How to return worksheet or workbook name in cell

I am trying to create a code that outputs the date (e.g. 201607) contained within the workbook name (e.g. 20160701_tyo). In the following code, I specify which cell to output the value, but I always run into an error that I didn't "define my object". What am I missing?

Sub WorksheetDateName()
    Dim DateName As String, OnlyDate As Long
    DateName = ActiveWorkbook.Name
    OnlyDate = Left(DateName.Value, 6)
    ActiveWorksheet.Range("E1").Value = OnlyDate
End Sub

Also, would it be possible to perform something similar for "ActiveSheet" in addition to "ActiveWorkbook"? Thank you in advance!

Upvotes: 0

Views: 227

Answers (1)

CLR
CLR

Reputation: 12279

For the Workbook name, you could use VBA: 

ActiveSheet.Range("E1").Value = Left(ActiveWorkbook.Name, 6)

Or you could just use a formula in E1 such as:

=MID(CELL("filename"),FIND("[",CELL("filename"))+1,6)

For the Worksheet name, if you wanted the first 6 characters you could use VBA:

 ActiveSheet.Range("F1").Value = Left(ActiveSheet.Name, 6)

and the equivalent formula would be:

=MID(CELL("filename"),FIND("]",CELL("filename"))+1,6)

Note: both Excel formula require that the file has been saved at least once previously to work correctly.

Looking through your code though, one way to 'fix' it would be to use Val to convert the 6 character string to a value, that can be held in the Long.

Sub WorksheetDateName()
    Dim DateName As String, OnlyDate As Long
    DateName = ActiveWorkbook.Name
    OnlyDate = Val(Left(DateName, 6))
    ActiveSheet.Range("G1").Value = OnlyDate
End Sub

You'll also note I've changed ActiveWorksheet to Activesheet - as other comments have suggested.

Upvotes: 3

Related Questions