Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 26

Thread: Display filename without extension

  1. #1

    Display filename without extension

    So I am trying to display a file name in a textbox, without the extension. Here is the code I am using to display the file name:

    [VBA]Me.TextBox1 = activedocument.name[/VBA]

    It shows up with the extension. Any ideas on how to get rid of it?

    thanks,
    Matt

  2. #2
    VBAX Wizard
    Joined
    May 2004
    Posts
    6,713
    Location
    [vba]Mid(ActiveDocument.Name, 1, Len(ActiveDocument.Name) - 4)[/vba]

    If it is docx or docm, use -5.

    My memory is faulty, so macropod will likely come up with a shorter way.

  3. #3
    Awesome! Thank you! I used the - 5 and it worked perfectly.

    thanks again

  4. #4
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    Location
    Try...
    [VBA]
    left(ActiveDocument.Name, instrrev(ActiveDocument.Name, ".") -1)
    [/vba]

  5. #5
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    Location
    Oh, and fumei... your memory is faulty only because you've forgotten more than most ever knew

  6. #6
    VBAX Wizard
    Joined
    May 2004
    Posts
    6,713
    Location
    Paul has made me nervous about using Left (and other string functions)....

  7. #7
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    Location
    Why? Because of this excellent thread?
    http://www.aivosto.com/vbtips/stringopt.html#whyslow

    One thing (apart from the optimization issues on string functions) I've encountered is needing to prepend VBA. on all of these kinds of functions, because of compile issues in various environments (so that VBA.Left compiles correctly, when Left would cause a compile issue), but other than that... what has Paul said?

  8. #8
    VBAX Wizard
    Joined
    May 2004
    Posts
    6,713
    Location
    Nah, it was my use of Left/Right and then Paul gave a Format solution that was much shorter.

  9. #9
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Hi Matt & Gerry,

    [VBA]Me.TextBox1 = Split(ActiveDocument.Name,".")(0)[/VBA]
    This will work with file extensions of any length.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  10. #10
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    Location
    You devil. That's brilliant.

  11. #11
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    [VBA]
    Me.TextBox1 = Split(ActiveDocument.Name,".")(0)
    [/VBA]

    Unless the document name contains periods:

    MyDoc.SubDoc.SubSubDoc.docx

    Paul

  12. #12
    VBAX Wizard
    Joined
    May 2004
    Posts
    6,713
    Location
    See? I told you.

  13. #13
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    Location
    Well, then I'm convinced. My solution is the best one.

    *grin*

  14. #14
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Well, then I'm convinced. My solution is the best one.
    Unless the document has not been saved, then this fails

    [VBA]
    left(ActiveDocument.Name, instrrev(ActiveDocument.Name, ".") -1)
    [/VBA]



    Maybe

    [VBA]
    Option Explicit
    Function JustTheName(Optional sFile As String = vbNullString) As String

    Dim i As Long

    If Len(sFile) = 0 Then sFile = ActiveDocument.Name

    i = InStrRev(sFile, ".")

    If i = 0 Then
    JustTheName = sFile
    Else
    JustTheName = Left(sFile, i - 1)
    End If
    End Function
    Sub test()
    MsgBox JustTheName

    MsgBox JustTheName("c:\My Documents\My Doc.SubCat.SubSubCat.docx")

    MsgBox JustTheName("My Documents\My Doc.SubCat.SubSubCat.docx")
    End Sub
    [/VBA]

    Paul

  15. #15
    VBAX Wizard
    Joined
    May 2004
    Posts
    6,713
    Location
    Hmmmmm. IMHO anyone who uses multiple dots in a filename deserves to get messed up. That is just poor naming.

  16. #16
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Quote Originally Posted by fumei
    Hmmmmm. IMHO anyone who uses multiple dots in a filename deserves to get messed up. That is just poor naming.
    Agreed. It's also a technique that's been exploited by people wanting to distribute malicious files - simply give an executable a name like somedocument.docx.exe and, when the user thinking it's a document double-clicks to open it, it executes. Especially dangerous for those users who have Windows configured (as per the default) not to show the extensions of known file types.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  17. #17
    VBAX Master
    Joined
    Feb 2011
    Posts
    1,480
    Location
    I've often simply used the test of...

    [vba]
    If ActiveDocument.Name = ActiveDocument.FullName Then
    'document has not yet been saved anywhere
    Else
    'document has been saved at some point
    End If
    [/vba]
    That can obviously be optimized by using Len, but I only optimize when it is more important than readability.

    Since .FullName gives the path and the name, that's my standard test to see if there is a path. Could also test to see if .Path is equal to a vbnullstring, etc. Lots of different approaches... but Paul_Hossler makes a good point, although not necessarily one the OP considered, as there is no file extension on an unsaved document.

  18. #18
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Hmmmmm. IMHO anyone who uses multiple dots in a filename deserves to get messed up. That is just poor naming.
    Interesting. We do this a lot as a technique for 'relating' files

    For example, 'Proposal for Bill.xlsm' will generate 'Proposal for Bill.data.xlsx' and 'Proposal for Bill.backup.txt' and 'Proposal for Bill.OfferLetter.docx' and 'Proposal for Bill.MgtCopy.docx' all in the 'Proposal for Bill.xlsm' folder

    Paul

  19. #19
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Quote Originally Posted by Paul_Hossler
    Interesting. We do this a lot as a technique for 'relating' files

    For example, 'Proposal for Bill.xlsm' will generate 'Proposal for Bill.data.xlsx' and 'Proposal for Bill.backup.txt' and 'Proposal for Bill.OfferLetter.docx' and 'Proposal for Bill.MgtCopy.docx' all in the 'Proposal for Bill.xlsm' folder

    Paul
    Not a good move. I'd recommend using underscores or hyphens instead of periods. In fact, almost any valid character other than periods, as those are meant to be name/extension delimiters. And you have a "'Proposal for Bill.xlsm' folder"? Bizarre, to say the least, to give a folder a filename extension.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  20. #20
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Bizarre, to say the least, to give a folder a filename extension.
    I meant that the XLSM will generate the other files in it's folder, giving them the Filename from the xlsm as a 'base' and the '.tag' and the extension for the file.

    Better example

    In folder = ..\Proposals\ there's a file named Proposal for Bill.xlsm

    Macros in that xlsm will generate

    Proposals\Proposal for Bill.data.xlsx
    Proposals\Proposal for Bill.backup.txt
    Proposals\Proposal for Bill.OfferLetter.docx
    Proposals\Proposal for Bill.MgtCopy.docx


    Underscores and hyphens could be used by others when naming files, making it a little more difficult.

    You both raise good points, so I'll have to think it over, and talk to some other people

    Paul

Posting Permissions

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