VBA Macro To Create An Excel File With Same Sheet Name As Workbook Name

**DocAElstein** · 09-06-2016, 09:39 PM

Originally Posted by snb

....
The first works if the workbook hasn't been saved yet.....

Ah,yes.. I thought i noticed it worked the first time.
_......

Originally Posted by snb

...
I prefer methods that serve a certain purpose (getbasename), instead of work'around ....
.

I agree the _....
= CreateObject("scripting.filesystemobject").getbase name(ThisWorkbook.FullName)
_...was a useful thing to know about.
_... and , ... It looks nicer than
= Left(Right(ThisWorkbook.FullName, Len(ThisWorkbook.FullName) – InStrRev(ThisWorkbook.FullName, “\”)), (InStrRev(Right(ThisWorkbook.FullName, Len(ThisWorkbook.FullName) – InStrRev(ThisWorkbook.FullName, “\”)), “.”) – 1))
. but in a code Module it does not look too bad... it vanishes to the right, and text is cheap. I like to have a few ways available, - you never know when one may fail !!

Code:

Sub GetThebaseNameBackToUs() 'http://www.excelfox.com/forum/showthread.php/2123-VBA-Macro-To-Create-An-Excel-File-With-Same-Sheet-Name-As-Workbook-Name?p=9952#post9952
10   Rem 1 ' Let Alan do it !
20   Dim FullFilePathAndName As String ' The longest String going right back and including the Full File Name
30    Let FullFilePathAndName = ThisWorkbook.FullName
40   Dim FullNameOnly As String 'File name as typically seen displayed with last bit after dot
50    Let FullNameOnly = Right(FullFilePathAndName, Len(FullFilePathAndName) - InStrRev(FullFilePathAndName, "\")) 'Full File including extension ( Bit after .  Dot )
60   Dim BaseNameOnly As String '
70    Let BaseNameOnly = Left(FullNameOnly, (InStrRev(FullNameOnly, ".") - 1)) 'To Take off the bit after the . dot
80   '
90   ' or alternative:
100   Let BaseNameOnly = Left(Right(ThisWorkbook.FullName, Len(ThisWorkbook.FullName) - InStrRev(ThisWorkbook.FullName, "\")), (InStrRev(Right(ThisWorkbook.FullName, Len(ThisWorkbook.FullName) - InStrRev(ThisWorkbook.FullName, "\")), ".") - 1))
110  '
115  Rem 2
120  ' or _..._... Do in snb stylio !
130   BaseNameOnly = CreateObject("scripting.filesystemobject").GetBaseName(ThisWorkbook.FullName)
End Sub