VBA – Get name of file without extension

The easiest way to get the name of a file is of course to use ThisWorkbook.Name. It will supply you with the name and the extension of the file (for example “MyWorkbook.xlsx”).But if you want to retrieve only the name the workbook and not the extension, you’ll need this:

Dim NameOfWorkbook

NameOfWorkbook = Left(ThisWorkbook.Name, (InStrRev(ThisWorkbook.Name, ".", -1, vbTextCompare) - 1))

It uses the function InStrRev to find the last occurance of “.” and the Left() function is then used to assign all chars left of this position to the NameOfWorkbook variable.

Sunday, December 16th, 2012 Uncategorized