Find/Replace links to files that don't exist yet.

[SandsB]

I have a new monthly file the reads several daily input files. The names of those input files include the date - opened reports 08-01-22.xlsx, etc. Today starts a new month so I copied the August's monthly file and want to do a rename to change all the links. If I do a find/replace to change "ts 08" to "ts 09" I'm prompted to point to daily input files that don't exist yet. I can click Cancel and the link is changed so I know a way to do this. But there are 18 links that need to change for each day and 30 days in September (500+ links) and I'd need to do this every month. Is there a way to make the changes all at once?

 

[Micron]

Something like this (or you might find similar posts)?

Redirecting

 

[SandsB]

I'm not great at VBA but this looks to me like it would work fine. But it doesn't and I don't see why. My ignorant guess is that it might have something to do with my link that includes a VLOOKUP and an IFERROR. Could that be it and, if so, is there a way around it? Here's one of the links:
=IFERROR(VLOOKUP($A5,'J:\Patient\Input\[opened reports 08-01-22.xlsx]Sheet1'!$A$1:$D$74,4,FALSE)*29,"")

Thank you

 

[SandsB]

I should have included this....

The macro doesn't error out. When I step through it goes directly from
For Each hyp In wsh.Hyperlinks
to
Next hyp
4 times then ends - it never goes to the lines to make the changes.

 

[Micron]

Your hyperlink is buried in a cell formula? Maybe post your code within code tags (vba button on posting toolbar)?

 

[SandsB]

Sorry, I'm not sure what you mean. The only thing I changed were the text strings to update the links. The original in the code you referred me to changed drive letters. I'm changing filenames.

Sub BatchEditHyperlinks()
Dim wsh As Worksheet
Dim hyp As Hyperlink
For Each wsh In ActiveWorkbook.Worksheets
For Each hyp In wsh.Hyperlinks
With hyp
.Address = Replace(.Address, "ts 08-", "ts 09-")
.TextToDisplay = Replace(.TextToDisplay, "ts 08-", "ts 09-")
End With
Next hyp
Next wsh
End Sub

 

[Micron]

You are free to ignore the request about code tags but then I won't bother to read it if it's too long and complicated.

Excel formulas are not my thing. To me, this is a hyperlink address buried in a formula
=IFERROR(VLOOKUP($A5,'J:\Patient\Input\[opened reports 08-01-22.xlsx]Sheet1'!$A$1:$D$74,4,FALSE)*29,"")

If you're trying to change that, I think it would be more like this in vba (I've used the long version but might elect to use variables instead)

Sheets("Sheet3").Range("B10").Formula = Replace(Sheets("Sheet3").Range("B10").Formula, "Sheet3!A10", "Sheet4!A10")

which would change something used in the cell formula. I can't tell what you're trying to change where, because the code you posted seems to have more to do with the collection of hyperlinks belonging to a sheet object, such as these

If this is what you have in the address bar below
=IFERROR(VLOOKUP($A5,'J:\Patient\Input\[opened reports 08-01-22.xlsx]Sheet1'!$A$1:$D$74,4,FALSE)*29,"")
then I have no idea how that would ever work or how to programmatically edit it. When I try to put that into the cell reference (1st image in this post) it won't accept it.

If I can't understand what you're trying to alter and where, then you'll have to wait for someone to come along who's better at this than I am.
EDIT - I think the property wrt 1st pic is named SubAddress, not Address so maybe that will help.

 

[SandsB]

The whole string I need to change is some version of this:
=IFERROR(VLOOKUP($A5,'J:\Patient\Input\[opened reports 08-01-22.xlsx]Sheet1'!$A$1:$D$74,4,FALSE)*29,"") There's about 20 formulas like this for each day of the month but they're all pretty much the same other than the referenced cell, pivot table range, etc.
The range of the pivot table, the specific cells and the multiplier don't need to change. For example, in my August worksheet,
=IFERROR(VLOOKUP($A5,'J:\Patient\Input\[opened reports 08-01-22.xlsx]Sheet1'!$A$1:$D$74,4,FALSE)*29,"")
needs to change to
=IFERROR(VLOOKUP($A5,'J:\Patient\Input\[opened reports 09-01-22.xlsx]Sheet1'!$A$1:$D$74,4,FALSE)*29,"")
in the September worksheet. Nothing needs to change in any of the 500+ links except the number of the month.
My code, copied from the example you pointed me to, does a find/replace on "ts 08-" with "ts 09-" just to get the tail end of the word "reports". A replacement of just 08 with 09 might change a pivot table range or a day instead of a month, etc.

This reply is to clear up what I'm looking for - You've already spent a lot of time on this and I appreciate it. I can get what I need by copying all of the August files, renaming them with 09 instead of 08, doing a find/replace all, and then deleting the copied files. The worksheet will give me blanks for days where the file hasn't been created yet.
Again thanks for the time - I'll hang onto the code you sent as a tool I can use in the future.

 

[Micron]

I appreciate that you're appreciative, if that makes sense. I'm thinking that the expression I gave you in post 7 ought to work. Here it is in the sheet sub I tested it in. It replaces the formula reference from Sheet3!A10 to Sheet4!A10. The revamped formula looks like this:
=IF(Sheet4!A10=0,"",Sheet4!A10)

The code in the link is not for that purpose. If you would try modifying it and testing, I'd appreciate knowing if it solves your issue. I just put the cursor in the first line and press F8 to go thru line by line.

Function alterFormula()

Application.EnableEvents = False
Sheets("Sheet3").Range("B10").Formula = Replace(Sheets("Sheet3").Range("B10").Formula, "Sheet3!A10", "Sheet4!A10")
Application.EnableEvents = True

End Function

I guess that for you, it would be more like (using variable to shorten code line(s))

Function alterFormula()
Dim str As String, strOut As String

Application.EnableEvents = False
strIn = "=IFERROR(VLOOKUP($A5,'J:\Patient\Input\[opened reports 08-01-22.xlsx]Sheet1'!$A$1:$D$74,4,FALSE)*29,"")
strOut = Replace(strIn, "reports 08", "reports 09")
Sheets("Sheet3").Range("B10").Formula = strOut

Application.EnableEvents = True

End Function

Don't forget to deal with your sheet name(s) and cell/range references if you decide to try, and just concentrate on changing one cell formula. If there are several, they can be dealt with later. If you can't get it to work, you can always upload a test file somewhere and I'll take a look. Note: you may not need to disable events; I just did so in testing because this ran other code I didn't want to run.