Change a filename in excel VBA to a new month e.g. accountsfeb.xls to accountsjan

Posted on 2006-03-29
Last Modified: 2012-05-05

On a worksheet I have a list of months with the current month in a cell say a1

Current Month:  March

and so on..

I want to open a series of wbs each month each path is the same apart from the month that changes e.g

FundingJan.xls becomes FundingFeb.xls

A series of cells are linked to these wbs, I can just update the links by passing a variable into a find and replace statement
is there a way to split the filename so that the month can be read off a worksheet list

e.g Funding&A1&.xls , which would become


Any Ideas, probably straight forward any ideas?
Question by:1benjiman
    LVL 1

    Accepted Solution

    Have a look at these two examples I quickly put together, they should point you in the right direction:

    'this example shows how to replace the month when you know where it is
    Sub SimpleReplace()
        Dim strFilename As String
        Dim strMonth As String
        Dim strNewFilename As String

        strFilename = "FundingFebruary.xls"
        'Assume cell A3 contains the month you want to switch in
        strNewFilename = Left(strFilename, 7) & Worksheets("SheetName").Range("A3") & ".xls"
        Debug.Print strNewFilename
    End Sub

    'this example searchs a list for current month and displays the next
    Sub ReplaceUsingOffsetToGetNextMonth()
        Dim oRange As Range
        Dim strCurrentMonth As String
        Dim strFilename As String
        Dim strMonth As String
        Dim strNewFilename As String
        strFilename = "FundingApril.xls"
        'work out month from filename
        strCurrentMonth = Mid(strFilename, 8, Len(strFilename) - 11)
        'set our range to find this month in the range of cells A3->A14 which contain months of the year
        Set oRange = Worksheets("SheetName").Range("A3:A14")
        'find current month in this range
        Set oRange = oRange.Find(What:=strCurrentMonth, LookIn:=xlValues)
        'get the next month
        If oRange.Offset(1, 0).Value = "" Then
            'we found the end of the list, get the first value
            strMonth = Worksheets("SheetName").Range("A3")
            'there is a value here, so return it
            strMonth = oRange.Offset(1, 0).Value
        End If
        strNewFilename = Left(strFilename, 7) & strMonth & ".xls"
        Debug.Print strNewFilename
        Set oRange = Nothing
    End Sub
    LVL 1

    Expert Comment


    Was I able to assist you or not with my answer?


    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Grammars for C C++ and java 1 98
    only14 challenge 19 57
    sumDigits  challenge 7 40
    mapShare challenge 13 41
    How to remove superseded packages in windows w60 or w61 installation media (.wim) or online system to prevent unnecessary space. w60 means Windows Vista or Windows Server 2008. w61 means Windows 7 or Windows Server 2008 R2. There are various …
    When we want to run, execute or repeat a statement multiple times, a loop is necessary. This article covers the two types of loops in Python: the while loop and the for loop.
    The goal of this video is to provide viewers with basic examples to understand and use switch statements in the C programming language.
    The viewer will learn how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now