Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    File renaming (Excel 2000)

    I posted this question in the Windows 2000 forum and it was suggested I also post it here. I need to rename a large group of files each month by adding the Month-Year to the filename so that Cash may 03.xls can become Cash june 03.xls , for instance. I was given a useful utility reference but I'm also wondering if Excel Macro can rename closed files. I didn't think so, so I posted a the Windows 2000 forum. Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: File renaming (Excel 2000)

    Here is a macro that will work from any application that supports VBA (Word, Excel, ...). The code is not Excel-specific.

    It will look at ALL .xls files in the folder specified in the constant strFolder (you must include the trailing backslash).
    If the file name already contains the current month, the file is skipped.
    If the file name contains the previous month, it is updated.
    If the file name doesn't contain the previous or current month, the current month is inserted.

    I suggest that you create a test folder to see if it does what you want before using it for real.

    <img src=/w3timages/blueline.gif width=33% height=2>

    Sub MassRename()
    ' Adapt path as needed; keep trailing backslash
    Const strFolder As String = "C:ExcelTest"
    Dim strPrevMonth As String
    Dim strCurrMonth As String
    Dim strFile As String
    Dim strNewFile As String
    Dim lngPos As Long

    strPrevMonth = Format(DateSerial(Year(Date), Month(Date) - 1, 1), " mmmm yy")
    strCurrMonth = Format(DateSerial(Year(Date), Month(Date), 1), " mmmm yy")

    strFile = Dir(strFolder & "*.xls")
    Do While strFile <> ""
    lngPos = InStr(strFile, strCurrMonth)
    If lngPos = 0 Then
    lngPos = InStr(strFile, strPrevMonth)
    If lngPos > 0 Then
    strNewFile = Left(strFile, lngPos - 1)
    Else
    strNewFile = Left(strFile, Len(strFile) - 4)
    End If
    strNewFile = strNewFile & strCurrMonth & ".xls"
    Name strFolder & strFile As strFolder & strNewFile
    End If
    strFile = Dir
    Loop
    End Sub

    <img src=/w3timages/blueline.gif width=33% height=2>

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File renaming (Excel 2000)

    Thanks for the solution. Will give it a try in the next few weeks, as we roll the month over.

Posting Permissions

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