We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

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

Medium Priority
563 Views
Last Modified: 2012-05-05
Hi,

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

Current Month:  March

january
february
march
april
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

FundingMarch.xls


Any Ideas, probably straight forward any ideas?
Comment
Watch Question

Commented:
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")
    Else
        '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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Commented:
Hi,

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

Thanks
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.