Error when trying to run code to edit links

[Lochagos]

Hi all, I've been struggling with editing the links contained in the cells of my excel sheet using VBA code. I currently have a large sheet with numerous cells, all containing links for data in another excel file. The sheet has been copied from a version from previous months, and as such I need to find and replace the months(and the year if needed) of every link so that it is the month before(i.e if it is currently April, the month in the links should be changed to March).

This is the code I'm currently using:

Sub test4()


    Dim a, b, x, y, z As Variant
    With ActiveSheet
        .Unprotect Password:="" '<-- if any
        y = Date - Day(Date)
        z = DateSerial(Year(Date), Month(Date) - 1, 0)
        x = DateSerial(Year(Date), Month(Date) - 2, 0)
        
        ThisWorkbook.ChangeLink "C:\Users\username\ABC International\(ABCD-FIN)-OJV-efile - Classification " & _
        "Type\Country\B1 group\Monthly financials reporting\" & Format$(x, "yyyy") & "\" & Format$(x, "yyyymm") & "\From Terminal\" & _
        "Monthly Report - " & Format$(x, "yyyy") & "(3rd Day)_group_" & Format$(y, "mmm") & ".xlsx", _
        "C:\Users\username\ABC International\(ABCD-FIN)-OJV-efile - Classification " & _
        "Type\Country\B1 group\Monthly financials reporting\" & Format$(z, "yyyy") & "\" & Format$(z, "yyyymm") & "\From Terminal\" & _
        "Monthly Report - " & Format$(z, "yyyy") & "(3rd Day)_group_" & Format$(y, "mmm") & ".xlsx"
            
            
    End With
End Sub

However when I try to run this code, it gives me a 'Run-time error '1004 Method ChangeLink of object '_Workbook failed'

Can anyone help with what's wrong with my link to cause this error to show up?

 

[RudRud]

Hi, All the replacement files name and location path are same? just different date/time frame?

If yes, Would you mid to provide us two files name here with two different dates/time frames?

 

[RudRud]

Book1
	L	M	N	O
1			Link	New link
2			E:\New folder\VBA LEARNING.xlsm	E:\New folder\VBA LEARNING 2023.xlsm
3
4
5
6
7
8
9
10
11	xx
Sheet1

Cell Formulas
Range	Formula
L11	L11	='E:\New folder\[VBA LEARNING 2023.xlsm]FIND ADDRESS'!$R$8

Optional for your reference,

I will link out activeworkbook linksources in N2, After listed out, I will manually put new link in O2

Then re-run the code to get the updated link

Sub test4()

ls = ActiveWorkbook.LinkSources
[n2].Resize(UBound(ls, 1)) = ls

For Each ss In Range("o2:o" & Cells(Rows.Count, "O").End(xlUp).Row + 1)
If ss.Value <> "" Then ThisWorkbook.ChangeLink ss.Offset(0, -1).Value, ss.Value
Next ss

[o2:o100].Clear
ls = ActiveWorkbook.LinkSources
[n2].Resize(UBound(ls, 1)) = ls
End Sub

 

[RudRud]

Sub test4()

For Each ss In Range("o2:o" & Cells(Rows.Count, "O").End(xlUp).Row + 1)
If ss.Value <> "" Then ThisWorkbook.ChangeLink ss.Offset(0, -1).Value, ss.Value
Next ss

[o2:o100].Clear
ls = ActiveWorkbook.LinkSources
[n2].Resize(UBound(ls, 1)) = ls
End Sub

New link can make it dynamic but depending your link name, so it would be good if you can provide us two link names

 

[Lochagos]

Sub test4()

For Each ss In Range("o2:o" & Cells(Rows.Count, "O").End(xlUp).Row + 1)
If ss.Value <> "" Then ThisWorkbook.ChangeLink ss.Offset(0, -1).Value, ss.Value
Next ss

[o2:o100].Clear
ls = ActiveWorkbook.LinkSources
[n2].Resize(UBound(ls, 1)) = ls
End Sub

New link can make it dynamic but depending your link name, so it would be good if you can provide us two link names

Hi, sorry for the late reply. Here is an example link that hopefully should be clearer: ='C:\Users\username\ABC International\(ABCD-FIN)-OJV-efile - Classification Type\Country\B1 group\Monthly financials reporting\2023\202302\From Terminal\[Monthly Report - 2023(3rd Day)_group_Feb.xlsx]PL Old_detail'!AS14

The sections that I've bolded are the areas that I want to be able to change and update with my code, namely the months and year. The original links would be for two months prior,(i.e if the current month is June, then the month in the links would be April), and I want to change it so that the months in the links are for the previous month instead(i.e. if the current month is June, then the month in the links would become May instead). The year should also be updated if there is a change.

 

[RudRud]

=

Book2
	H	I	J	K	L	M	N	O	P
1							Current Link	Change Desire link F5 Code	2
2							E:\ABC International\(ABCD-FIN)-OJV-efile - Classification Type\Country\B1 group\Monthly financials reporting\2023\202302\From Terminal\Monthly Report - 2023(3rd Day)_group_Feb.xlsx	E:\ABC International\(ABCD-FIN)-OJV-efile - Classification Type\Country\B1 group\Monthly financials reporting\2023\202302\From Terminal\Monthly Report - 2023(3rd Day)_group_Feb.xlsx
3
4
5	123123213
Sheet1

Cell Formulas
Range	Formula
O2	O2	=LET( Yr,YEAR(TODAY()), num,MONTH(TODAY())-P1, MonthValue,MONTH(EOMONTH(TODAY(),-num)), Monthxlsx,TEXT(EOMONTH(TODAY(),-num),"mmm")&".xlsx", yearwithmth,Yr&"0"&MonthValue, "E:\ABC International\(ABCD-FIN)-OJV-efile - Classification Type\Country\B1 group\Monthly financials reporting\"&Yr&"\"&yearwithmth&"\From Terminal\Monthly Report - "&Yr&"(3rd Day)_group_"&Monthxlsx)
H5	H5	='[Monthly Report - 2023(3rd Day)_group_Feb.xlsx]PL Old_detail'!$AS$14

Hi, Kindly give a try, You may run the code then input the month that you desire to change( for example this is month is june then you just put 5 in the input box).

Still can shorten the code but maybe can try 1st if can run in your site (before running the code make sure to put the let formula in o2 or desire column which's adjustable)

Sub test4()

ls = ActiveWorkbook.LinkSources
[n2].Resize(UBound(ls, 1)) = ls

mont = Application.InputBox("Month:")

[p1].Value = mont

For Each ss In Range("o2:o" & Cells(Rows.Count, "O").End(xlUp).Row + 1)
If ss.Value <> "" Then ThisWorkbook.ChangeLink ss.Offset(0, -1).Value, ss.Value
Next ss


ls = ActiveWorkbook.LinkSources
[n2].Resize(UBound(ls, 1)) = ls


End Sub