Results 1 to 2 of 2
2008-08-29, 05:21 #1
- Join Date
- Feb 2004
- Houston, Texas, USA
- Thanked 0 Times in 0 Posts
Truncate Filename to First Blank Space (2000/SP3)
I receive about 60 files every month from different people that are named with a code and a text description, e.g.,
A12345 Text Description1.xls
A67891.01 Text Description2.xls
In order to process them, I have to rename them to just the code, e.g.,
Is there any way that I can create a macro or batch file that will run against all files in a directory and truncate the file name as far as the first blank space?
I've figured out how to do it in reverse with a DOS BAT file, going from the code.xls to the code with text description. However, because the text description on the files that I receive varies from month to month and file to file, I haven't been able to figure out how to go from code with text description to just code.
Thanks for any help with this,
2008-08-29, 08:35 #2
- Join Date
- Mar 2002
- Thanked 29 Times in 29 Posts
Re: Truncate Filename to First Blank Space (2000/SP3)
Place all workbooks in the same folder, and don't put any other workbooks in that folder. You can then run the following macro, after modifying the strFolder constant:
' Modify as needed but keep trailing backslash
Const strFolder = "C:Excel"
Dim strFile As String
Dim strNew As String
Dim intPos As String
strFile = Dir(strFolder & "*.xls")
Do While Not strFile = ""
intPos = InStr(strFile, " ")
If intPos > 0 Then
strNew = Left(strFile, intPos - 1) & ".xls"
Name strFolder & strFile As strFolder & strNew
strFile = Dir