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


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?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

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

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming Languages-Other

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.