Auto Updating Cell Values in Excel using VB

Question

I have 2 Excel files named September and October. The September file contains values from which the October file refers/links to using the formula:

=+B2+'C:\\[September.xlsx]Sheet1'!A1 

so cell B2 in October contains a value that is the sum of B2 and A1 which comes from the September file.

If I now create a November file, I would simply do a Save As on the October file and save the file as November.xlsx. However, this means that the November file is still referring to values September.xlsx. Is there any way of automatically updating the cells in November.xlsx to refer to October, either upon creation or opening of the November file?

i.e. so November's formula would automatically update to =+B2+'C:\[October.xlsx]Sheet1'!A1.

Or making a Window form pop up when opening the file, asking the month it would like it to link to, the user would then enter in a month then all the cells in range would be updated.

Is anyone able to point me in the right direction?

Answer 1

A simple find and replace will work with this kind of link.

You'll have to skip over cells that use the name as a label that you don't want to change. You can also do this in vba by looking only at formulas. Here is a post about how to do that.

Answer 2

You can use the Workbook.ChangeLink method.

expression.ChangeLink(Name, NewName, Type)

where Name is the existing file name, NewName is the new file name

To see it in action, try recording a macro while changing the link source manually, and examine the resulting code. Access it from Data/Connections/Edit Links menu

Probably the simplest implementation would be to write a macro to do the SaveAs and ChangeLink in one go. Or leverage the BeforeSave event.

Answer 3

Try recording a macro while you save your October workbook as November.xlsx and then editing the links in the new workbook to link to October.xlsx.

The raw code will be quite messy but you should be able to edit it to suit other months.

Answer 4

This purely formula-based solution works for me. It is spread across several cells. I imagine you could combine all the formulas in to one very long one in a singe cell, but in my view it's preferable not to. I would just tuck the intermediate cells (cells F12-F17 in my example) away somewhere out of the way.

The formulas:

Cell   Formula
----   ---------------------------------------------------------
F12    =CELL("filename",A1)

F13    =MID(F12,FIND("[",F12)+1,FIND("]",F12)-FIND("[",F12)-1)

F14    =LEFT(F13,FIND(CHAR(1),SUBSTITUTE(F13,".",
            CHAR(1),LEN(F13)-LEN(SUBSTITUTE(F13,".",""))))-1)

F15    =TEXT(DATE(2000,MONTH(DATEVALUE("1 " & F14))-1,1),"mmmm")

F16    =F15 & MID(F13,FIND(CHAR(1),SUBSTITUTE(F13,".",
            CHAR(1),LEN(F13)-LEN(SUBSTITUTE(F13,".","")))),LEN(F13))

F17    =SUBSTITUTE(F12,F13,F16)

F18    =INDIRECT(ADDRESS(1,1,,,F17))

The results, assuming the current sheet is saved as December.xlsx:

Cell:  Value:
-----  ---------------------------------------------------------
F12    C:\Users\user.name\Documents\[December.xlsx]Sheet1
F13    December.xlsx
F14    December
F15    November
F16    November.xlsx
F17    C:\Users\user.name\Documents\[November.xlsx]Sheet1
F18    value from cell A1 in November.xlsx!

Notes:

• F12 Gets full path of current workbook. May need to manually update calcs (press F9) for this cell to refresh
• F13 Extracts filename from between '[' and ']' characters
• F14 Removes extension (everything after last '.') to get the file's base name (i.e. this month's name)
• F15 Geta name of previous month
• F16 Appends the extension that was removed earlier, to get filename of previous month's sheet.
• F17 Substitutes this filename into the current workbook's path. Here we assume that this month's workbook is saved in the same folder as the previous month's workbook.
• F18 Gets value from row 1, column 1 (cell A1) in previous month's sheet