• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 547
  • Last Modified:

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

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?
0
1benjiman
Asked:
1benjiman
  • 2
1 Solution
 
WiGgLrCommented:
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
0
 
WiGgLrCommented:
Hi,

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

Thanks
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now