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

[DocAElstein]

or ?

........

Hi snb
The second code saves as a .csv File.
So does the first after I spotted the deliberate mistake

Sub M_snb1() ' http://www.excelfox.com/forum/showthread.php/2123-VBA-Macro-To-Create-An-Excel-File-With-Same-Sheet-Name-As-Workbook-Name?p=9950#post9950
    ThisWorkbook.Sheets(1).Copy
    With ActiveWorkbook
    Dim MyFileAWNameOnly As String: Let MyFileAWNameOnly = Left(ThisWorkbook.Name, (InStrRev(ThisWorkbook.Name, ".") - 1))
         .SaveAs MyFileAWNameOnly, 23
         .Close 0
     End With
End Sub

Alan

[snb]

@Doc

Avoid Let , it's redundant.
That's also the case of any variable in this code.
The declaration makes no difference either.
I prefer methods that serve a certain purpose (getbasename), instead of work'around and around and aroud's.

The first works if the workbook hasn't been saved yet.
The secound one is necessary when a workbook has an extension that contradicts the filetype.

[DocAElstein]

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

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

...
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 !!

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

_.......

... Let , it's redundant......

We know..
http://www.excelfox.com/forum/showth...=9701#post9701
http://www.excelfox.com/forum/showth...page2#post9459
_ :-)
Alan

[snb]

Why not ?

Sub M_snb()
  MsgBox Split(ThisWorkbook.Name, ".")(0)
End Sub

[DocAElstein]

seems a very efficient use of a VBA Strings Function
This is not so good , but the best I could think of
Trim(Left(Replace(ThisWorkbook.Name, ".", Space(100), 1, 1), 50))