问题描述:

I want a macro that will at the start of a year change, for example,

Workbooks("cash 2014.xlsx").Worksheets("sum").Activate

to

Workbooks("cash 2015.xlsx").Worksheets("sum").Activate

I've created the steps that on 1/1/2015 would give me d = 2015, so that I can write

y = "cash " & d & ".xlsx", which yields cash 2015.xlsx (with no quotes).

If I then write

Workbooks(y).Worksheets("sum").Activate,

or

Workbooks("y").Worksheets("sum").Activate,

Excel won't run either one.

VBA also doesn't recognize:

Workbooks(""cash " & d & ".xlsx"").Activate

or

Workbooks('"cash " & d & ".xlsx"').Activate

or

Workbooks("'cash ' & d & '.xlsx').Activate

网友答案:

You can use:

Workbooks("cash" & d & ".xlsx").Activate

Or

y = "cash " & d & ".xlsx"
Workbooks(y).activate

If you put quotes around your variables they become strings and are intepreted as just d or y

Also, it's best to come up with names for your variables so the next person that has to maintain your code doesn't hunt you down at your new job and stab your eyeballs out with a pair of chopsticks while screaming obscenities at you.

网友答案:

If you want to break down the old name and rename it with a new year, try this.

As long as the form is "AAAA ####.ext" with a space between the name and year.

This will take the old name, break out the year, add one, then Save As the new name.

Sub RenameNewYear()
Dim oldPath As String, path As String, OldName As String, fileExt As String
Dim tempYear As Long, newYear As Long
Dim old() As String
Dim Filename As String, FileNPath As String

    path = ActiveWorkbook.path    'C:\users\owner\desktop
    OldName = ActiveWorkbook.Name '"cash 2015.xlsm"   

    'Split original name up into sections.  
    old = Split(OldName, ".")     'old(0) = "cash 2014", old(1) = "xlsm"
    fileExt = "." & old(1)        '.xlsm
    old = Split(old(0), " ")      'old(0) = "cash", old(1) = "2014"

    tempYear = CLng(old(1))       '2014
    newYear = tempYear + 1        '2015

    Filename = (old(0) & " " & newYear & fileExt)   '"cash 2015.xlsm"

    FileNPath = path & "\" & Filename           'C:\users\owner\desktop\cash 2015.xlsm

    ActiveWorkbook.SaveAs fileName:=FileNPath
    Sheets("sum").Activate
End Sub

To open it you'd use this: But it would already be open, as the last code saves As the new name.

Workbooks.Open Filename:=FileNPath
相关阅读:
Top