Printable View
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
[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.
Awesome! Thank you! I used the - 5 and it worked perfectly.
thanks again
Try...
[VBA]
left(ActiveDocument.Name, instrrev(ActiveDocument.Name, ".") -1)
[/vba]
Oh, and fumei... your memory is faulty only because you've forgotten more than most ever knew ;)
Paul has made me nervous about using Left (and other string functions)....
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?
Nah, it was my use of Left/Right and then Paul gave a Format solution that was much shorter.
Hi Matt & Gerry,
[VBA]Me.TextBox1 = Split(ActiveDocument.Name,".")(0)[/VBA]
This will work with file extensions of any length.
You devil. That's brilliant.
[VBA]
Me.TextBox1 = Split(ActiveDocument.Name,".")(0)
[/VBA]
Unless the document name contains periods:
MyDoc.SubDoc.SubSubDoc.docx
Paul
See? I told you.
Well, then I'm convinced. My solution is the best one.
*grin*
Unless the document has not been saved, then this failsQuote:
Well, then I'm convinced. My solution is the best one.
[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
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.Quote:
Originally Posted by fumei
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.
Interesting. We do this a lot as a technique for 'relating' filesQuote:
Hmmmmm. IMHO anyone who uses multiple dots in a filename deserves to get messed up. That is just poor naming.
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.Quote:
Originally Posted by Paul_Hossler
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.Quote:
Bizarre, to say the least, to give a folder a filename extension.
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
Agreed. It's also a technique that's been exploited by people wanting to distribute malicious files
Seriously?
Well then. I am completely, utterly with macropod on this - use underscores. ESPECIALLY if you raise the issue of multiple dots being malicious. Sheeesh. Really? I have been away too long. But then, I NEVER click open files from email. Ever. And when I was working, we locked users from doing it. They had to save files before opening them. So scanning was done at the front-end (incoming email) and at the (sort of) back end (when they saved files).
I still think using dots in filenames is a bad bad idea. Or at least a very poor idea.
Unfortunately, it's not just the user that does this. Some DMS's have the ability to export large numbers of files, and the choice is to export the document using the document description (a name) or the document number.
In one case you might get
124453_5.docx (doc # 124453, version5)
but in the other you might get
Ltr. to client A regarding Mr. Smith's such and such proposal.docx
Since the document "description" field in the DMS is a totally open text field, end-users will type whatever they want there. And they will naturally use periods sometimes, despite training/policies to try to avoid special characters in those fields.
So, at the end of the day, you will still end up with scenarios where your macro may be dealing with erroneous periods not related to the actual doc extension. I absolutely agree with not creating systems which use periods, if at all possible. I think using an underscore instead is a good best practice.
A few code tweaks would prevent users including periods in filenames. I doubt anyone would be seriously out out by seeing:
Ltr to client A regarding Mr Smith's such and such proposal.docx
instead of:
Ltr. to client A regarding Mr. Smith's such and such proposal.docx
Well, true... if you could capture all the various ways to input that info.
But most DMS's have multiple ways to enter documents into the DMS (Save As from Word being only one of them... there's also drag n' drop from an email attachment, some kind of "Import File" function, etc), at which point you can input profile info and never have interacted with VBA at all.
Of course, you can always write code to fix such things... but a minute of training can often supplement a month of coding for things like this. At the end of the day-- nothing is idiot proof.
It's a really good point that malicious attacks tend to hide behind use of multiple periods in file names, so I add that info in the answer to "Why shouldn't I use periods in my document description?" rather than try to solve it in what would be a really heavy custom code foot print.
However-- this discussion will probably have me on the phone for an enhancement request to Interwoven/Autonomy/HP/Filesite/Whatever-it's-called-these-days. I'd never thought of the malicious distribution issue before.
I really think - IMO - that it is an issue of best practices. And that being said, I think using dots in filenames is NOT a best practice. I also think overly extended file names themselves are not a best ptactice. If there is a serious requirement for very long names with some sort of separator between words, then there are alternatives - valid alternatives - to using dots. I fail to see there being any REAL need for using dots. And there is a decided negative to using dots. So....why use dots?
Thank you. I used this too and it works perfectly.