Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15

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

  1. #11
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    Quote Originally Posted by snb View Post
    or ?

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

    Code:
    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
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  2. #12
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    12
    @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.
    Last edited by snb; 09-06-2016 at 08:06 PM.

  3. #13
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    Quote Originally Posted by snb View Post
    ....
    The first works if the workbook hasn't been saved yet.....
    Ah,yes.. I thought i noticed it worked the first time.
    _......
    Quote Originally Posted by snb View Post
    ...
    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

    _.......
    Quote Originally Posted by snb View Post
    ... Let , it's redundant......
    We know..
    http://www.excelfox.com/forum/showth...=9701#post9701
    http://www.excelfox.com/forum/showth...page2#post9459
    _ :-)
    Alan
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  4. #14
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    12
    Why not ?

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

  5. #15
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    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))
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

Similar Threads

  1. Replies: 4
    Last Post: 07-02-2013, 11:32 AM
  2. Replies: 4
    Last Post: 06-18-2013, 01:38 PM
  3. VBA To Create A New Workbook
    By cdurfey in forum Excel Help
    Replies: 9
    Last Post: 05-23-2013, 06:41 PM
  4. Excel VBA Macro To Open A File Through Browse Dialog Box
    By Safal Shrestha in forum Excel Help
    Replies: 2
    Last Post: 04-05-2013, 12:59 PM
  5. Replies: 1
    Last Post: 06-02-2011, 10:38 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •