View Full Version : Macro to Edit Links ?

09-27-2016, 12:22 AM
Hi All,

I'm currently in the middle of a project that will go on for a couple of months, and each day i need to look up to the previous days file, which I have managed to do with formulas and then edit links.

The problem is, i need to open yesterdays file and the previous day to that otherwise the file always seems to crash. I was wondering if by implementing a Macro to edit the links it would make it less likely to crash?

I have extremely limited VBA knowledge and currently dont even have a basis to go from, but if the general consensus thinks that a VBA would help, then I will look to implement this, but would need some more assistance

Any help would be massively appreciated,


09-27-2016, 12:54 AM
You haven't given us enough information to get anywhere near helping you, Can you post an example workbooks with data in it, ( sanitised if necessary).
You can upload a sample workbook by using the "Manage Attachments" button below the Advanced Editor

09-27-2016, 01:03 AM
I was initially just trying to understand whether a macro in this instance would allow me to edit links without opening previous documents or cause the same crashes, thanks

09-27-2016, 01:10 AM
A macro can certainly edit links. whether it will prevent excel crashing is unknown until the cause of the crashes is ascertained.
You state that you are looking at the two previous days datafile by using links, it is possible that you could use a macro to grab the data from the two previous days without using links, If the links are the cause of the problem then this is an alternative approach. Without knowing exactly what you are trying to do, it is impossible to give you any detailed advice.

09-27-2016, 07:28 AM
Apologies - i will post an example shortly. Thanks for your help thus far.

09-27-2016, 07:53 AM
Added - Column B on both tabs is where the problem is arising

09-27-2016, 03:17 PM
I have had a look at the file you posted, but this is only one of the files , You haven’t posted the 22.09.16 X_Report.xls file.
Is this file exactly the same structure as the X report that you have posted?
If this is the case you appear to be trying to match a Serial code in F3 with a date in column D , which is never going to match. [X-M] tab
Similarly on the B-M tab you seem to matching a serial code with Age(Days).

Also it would appear that you are using Vlookup just to detect whether something exists. There is a specific function for doing this in excel “Match”. This is function is much simpler and faster than vlookup. I would suggest changing to use it. It might solve the crashing problem

I have been trying to work out what you are really trying to do. Are you just trying to detect whether a specific entry for serial code appeared on the previous sheet. Because if this is the case then it is very easy to do this totally automatically with a macro directly. Exactly how to do it would depend on whether you have an algorithm for the naming of the previous file and it’s location. E.g is the file The “last working day date” concatenated with X_report.xls. This is easy to work out until you get to Bank holidays.

If you want a macro to go down column B and just change the name of the file that is easy to do as well . You still need to work out what the algorithm is for the file naming convention. Or do you want the user to enter the file name or browse to select the file.

09-28-2016, 04:16 AM
Yeah, the 22.09.16 is exactly the same, just overwrite a template and save down daily.

Ahh - thanks for the help re. Match function, ill get that used now and update the file.

Yes - you've hit the nail on the head there, im trying to detect if the serial code from todays sheet appeared on yesterdays sheet. Each file is saved today with yesterdays date, so for instance today is 28.09, the file will be saved as "27.09.2016 X Report" (But this shouldnt include weekends). Bank holidays wouldnt be a problem per se in my requirements, i would be running it regardless.

Basically what i am trying to achieve is to have a macro look up to yesterdays report on the serial code, check if its there, if it is say "Seen Before", if not say "New". Then to save it correctly to todays (yesterdays) date in the folder, apologies for the stretched out explanations and clear lack of excel understanding haha. Much appreciated, Craig.

09-28-2016, 05:52 AM
This subroutine will change just the date part of the filename referenced in column B of your worksheet, this should work even after you have changed to the Match function

Sub changename()
newdate = InputBox("Enter the date for new filename; format DD.MM.YY")
With ActiveSheet
lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
inarr = Range(Cells(3, 2), Cells(lastrow, 2)).Formula

Application.EnableEvents = False

For i = 1 To lastrow - 2
formstr = inarr(i, 1)
stb = InStr(formstr, "[")
inarr(i, 1) = Left(formstr, stb) & newdate & Mid(formstr, stb + 9, 1000)

Next i
Range(Cells(3, 2), Cells(lastrow, 2)).Formula = inarr

Application.EnableEvents = True
End With

09-28-2016, 07:48 AM
Seems to work perfectly so far. Just going to implement a couple of changes - it may be that i need a tweak or two after that. Cant thank you enough for the help.

09-29-2016, 12:54 AM
I have amended the match function which has now alleviated much of the issue. Quick question - where in the macro will i amend the file path to ensure it is saving in the correct location?

09-29-2016, 02:19 AM
The macro doesn't use a file path, the macro does what you originally asked for it edits the links in Column B. It does this by searching for square bracket "[" in the formulae in column B and subtituting the 8 characters with the 8 characters that you type into Input box. It does this on the current active sheet, so you can run the macro on both of the sheets in your workbook just by selecting the sheets in turn.

09-29-2016, 02:37 AM
Ahhhh. My apologies. Yes thats perfect then, Thanks for your help.