Change code without extensions

[dotchieJack]

Hello dear helpers
I have this code

ActiveSheet.Range("B2") = ActiveWorkbook.Name

But I want it without the extention
Thanks in advance

 

[Debaser]

ActiveSheet.Range("B2").Value2 = left$(activeworkbook.Name, instrrev(activeworkbook.Name, ".") - 1)

 

[dotchieJack]

Hello Debaser, this works great, thank you
You are fast my friend

 

[YasserKhalil]

This would solve it too

ActiveSheet.Range("B2") = Split(ActiveWorkbook.Name, ".")(0)

 

[dotchieJack]

Thank you Yasser, i will test it tomorrow
you look verry young to write code

 

[jindon]

This would solve it too

ActiveSheet.Range("B2") = Split(ActiveWorkbook.Name, ".")(0)

Would fail for the file name like "ThisIs.MyBook.xlsm"
Alternative

ActiveSheet.Range("B2") = CreateObject("Scripting.FileSystemObject").GetBaseName(ThisWorkbook.Name)

 

[dotchieJack]

Hello Jindon,
Thank you for your reply, I will test it, I know you are a great teacher

 

[jindon]

ActiveSheet.Range("B2").Value2 = left$(activeworkbook.Name, instrrev(activeworkbook.Name, ".") - 1)

I don't want to do this, but it also fails when the workbook is not saved...

 

[Debaser]

That is true. If that is a possibility, you'd need to test first:

Dim lDot as Long
lDot = instrrev(activeworkbook.Name, ".")
If lDot = 0 then
ActiveSheet.Range("B2").Value2 = activeworkbook.Name
else
ActiveSheet.Range("B2").Value2 = left$(activeworkbook.Name, lDot - 1)
End If

 

[Marc L]

Hi !

Or just check if Workbook.Path is empty or not …
​