1

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?

Improve this question
1
  • Try to write the updation code in the open event method of the excel. Commented Nov 18, 2013 at 4:31

4 Answers 4

Reset to default
2

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.

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I have tried the Find and Replace method but not as specifically as you have set it like above. The way it's currently being done is, after saving as to the newer month, in this case November, all that's been done is change the cell formula from the top cell e.g. =+B2+'C:\[September.xlsx]Sheet1'!A1 to =+B2+'C:\[October.xlsx]Sheet1'!A1 then simply doing a drag down to all the cells below (B3, B4, B5 etc) to change/update their formula to October.xlsx. Both options are fine I am just trying to learn new methods and make it so you don't have to modify anything in the newly created spreadsheet.
+1 the most easiest way :) @bertrix: But why do you need to drag the formula? If you do Replace All then it will find an replace all instances. Also if you change the within from sheet to workbook then it will change in all sheets as well.
@bertrix: Also to be sure that you don't replace some other text by mistake search for \\[September.xlsx] and replace with \\[October.xlsx]
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.

Improve this answer

Comments

0

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.

Improve this answer

2 Comments

If you have difficulty modifying the recorded code, then at least you will something to post showing the attempt to create a VBA solution.
Thanks, Mark. I'll try making the macro starting from the Save As event and go from there.
0

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
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.