Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Feb 2004
    Location
    Houston, Texas, USA
    Posts
    95
    Thanks
    0
    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.,
    A12345.xls
    A67891.01.xls

    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,
    Caroline

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    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:

    Sub RenameFiles()
    ' 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
    End If
    strFile = Dir
    Loop
    End Sub

Posting Permissions

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