File name to appear in cell when workbook opened

[russelldt]

I am working with excel 365. Files are accessed online using One Drive

I would like to insert the file name (no path) in a particular cell, when I open the file

Can anyone suggest a solution. @filenamemdoes not work

Thank you

 

[kevin9999]

Try the following in the Workbook module of the file in question. Change the sheet/address to suit:

Private Sub Workbook_Open()
    Sheet1.Range("A1") = Left(ThisWorkbook.Name, InStr(ThisWorkbook.Name, ".") - 1)
End Sub

 

[Micron]

IMO you need to use the workbook open event. Off the top of my head, something like

Private Sub Workbook_Open()
Sheets("Sheet1").Range("A1") = ThisWorkbook.Name
End Sub

Not sure it makes sense to do this every time you open the workbook seeing as how the value will already be there the first time you open the wb.
Change your sheet and cell references of course.

 

[russelldt]

Try the following in the Workbook module of the file in question. Change the sheet/address to suit:

Private Sub Workbook_Open()
    Sheet1.Range("A1") = Left(ThisWorkbook.Name, InStr(ThisWorkbook.Name, ".") - 1)
End Sub

Try the following in the Workbook module of the file in question. Change the sheet/address to suit:

Private Sub Workbook_Open()
    Sheet1.Range("A1") = Left(ThisWorkbook.Name, InStr(ThisWorkbook.Name, ".") - 1)
End Sub

Yep, that worked, thanks.

 

[Fluff]

Another option using a formula

=TEXTBEFORE(TEXTAFTER(CELL("filename",A1),"[",-1),"]",-1)