Office 365 hardcoded links #ref! error after reopenening destination sheet

[Paigan]

Hello everybody.

I have been going crazy over this and although there are multiple threads regarding this problem none of them have helped so far.

The Problem:
There is a destination sheet that collects data from multiple Source sheets (12 atm, but may become more).
The links have mostly been generated by replacing file names via the search and replace function.

As can be seen in my example I have used the long form of hardcoding the Links.
One of my longer formulas now looks like this:

=(IF((SUMIFS('C:\Users\Name\Desktop\Company Name\Current task\Reporting\Program Name\New Reporting\[XXX XXX 05.2014.xlsx]general_report'!$K$2:$K$995'C:\Users\Name\Desktop\Company Name\Current task\Reporting\Program Name\New Reporting\[XXX XXX 05.2014.xlsx]general_report'!$B$2:$B$995;"Name")/3600)-(C74*8)>0;SUMIFS('C:\Users\Name\Desktop\Company Name\Current task\Reporting\Program Name\New Reporting\[XXX XXX 05.2014.xlsx]general_report'!$K$2:$K$995;'C:\Users\Name\Desktop\Company Name\Current task\Reporting\Program Name\New Reporting\[XXX XXX 05.2014.xlsx]general_report'!$B$2:$B$995;"Name")/3600-(C74*8);"0"))

So you can see my Links are hardcoded to a specific destination.

They do work if I open the source files along with my destination sheet. It is just on start up when I want to update data that the #ref errors appear.

Measures taken so far:
breaking the links and reapplying them via the Excel edit links function
using the shortened versions of the file links
saving the sheet in multiple file versions (binary sheet, '03 Compatibility, Macroenabled and normal '13 workbook) to work around possible formula length limits.

Does anyone have a good idea how to fix this?
As I heard there may be a possible workaround with VBA by programming it to open and close the source sheet with the destination sheet while updating. While this may serve as a short-term workaround I would really appreciate a long term solution.

Best

Paigan

 

[Paigan]

While I am still hoping for a longterm solution I have a question regarding my workaround.
This is my very first VBA module so there is probably way better solutions to this, but this is what I have come up with as a workaround:

Sub refreshsheet()
' opens and closes all needed sheets to refresh destination sheet
    MsgBox "Data is being updated, this may take a while"
    
    Workbooks.Open filename:="C:\Users\Name\Desktop\Company Name\Current task\Reporting\XYZ\Reporting\Monthly\ZYX 01.2014.xlsx"
    Workbooks.Open filename:="C:\Users\Name\Desktop\Company Name\Current task\Reporting\XYZ\Reporting\Monthly\ZYX 02.2014.xlsx"
    Workbooks.Open filename:="C:\Users\Name\Desktop\Company Name\Current task\Reporting\XYZ\Reporting\Monthly\ZYX 03.2014.xlsx"
    Workbooks.Open filename:="C:\Users\Name\Desktop\Company Name\Current task\Reporting\XYZ\Reporting\Monthly\ZYX 04.2014.xlsx"
    Workbooks.Open filename:="C:\Users\Name\Desktop\Company Name\Current task\Reporting\XYZ\Reporting\Monthly\ZYX 05.2014.xlsx"
    Workbooks.Open filename:="C:\Users\Name\Desktop\Company Name\Current task\Reporting\XYZ\Reporting\Monthly\ZYX 06.2014.xlsx"
    Workbooks.Open filename:="C:\Users\Name\Desktop\Company Name\Current task\Reporting\XYZ\Reporting\Monthly\ZYX 07.2014.xlsx"
    Workbooks.Open filename:="C:\Users\Name\Desktop\Company Name\Current task\Reporting\XYZ\Reporting\Monthly\ZYX 08.2014.xlsx"
    Workbooks.Open filename:="C:\Users\Name\Desktop\Company Name\Current task\Reporting\XYZ\Reporting\Monthly\ZYX 09.2014.xlsx"
    Workbooks.Open filename:="C:\Users\Name\Desktop\Company Name\Current task\Reporting\XYZ\Reporting\Monthly\ZYX 10.2014.xlsx"
    Workbooks.Open filename:="C:\Users\Name\Desktop\Company Name\Current task\Reporting\XYZ\Reporting\Monthly\ZYX 11.2014.xlsx"
    Workbooks.Open filename:="C:\Users\Name\Desktop\Company Name\Current task\Reporting\XYZ\Reporting\Monthly\ZYX 12.2014.xlsx"
    
        Windows("Final Report - All Months - Test - Kopie.xlsm").Activate
        ActiveWorkbook.RefreshAll
    
    Windows("ZYX 01.2014.xlsx").Activate
    ActiveWindow.Close
    Windows("ZYX 02.2014.xlsx").Activate
    ActiveWindow.Close
    Windows("ZYX 03.2014.xlsx").Activate
    ActiveWindow.Close
    Windows("ZYX 04.2014.xlsx").Activate
    ActiveWindow.Close
    Windows("ZYX 05.2014.xlsx").Activate
    ActiveWindow.Close
    Windows("ZYX 06.2014.xlsx").Activate
    ActiveWindow.Close
    Windows("ZYX 07.2014.xlsx").Activate
    ActiveWindow.Close
    Windows("ZYX 08.2014.xlsx").Activate
    ActiveWindow.Close
    Windows("ZYX 09.2014.xlsx").Activate
    ActiveWindow.Close
    Windows("ZYX 10.2014.xlsx").Activate
    ActiveWindow.Close
    Windows("ZYX 11.2014.xlsx").Activate
    ActiveWindow.Close
    Windows("ZYX 12.2014.xlsx").Activate
    ActiveWindow.Close
    
    Windows("Final Report - All Months - Test - Kopie.xlsm").Activate
    MsgBox "Thank you for your patience"
End Sub

The path of the final report is: C:\Users\Name\Desktop\Company Name\Current task\Reporting\XYZ\Reporting\Final Report - All Months - Test - Kopie.xlsm

My question is:
How can I adress the individual sheets as relative paths so I am able to share this report with my colleagues?

Best Paigan

 

[Paigan]

Easiest workaround I was able to come up with until now if anyone else was struggling with a similar proplem, though still interested in final solution with hardcoded links updating from closed sheets:

Private Sub CommandButton1_Click()
    MsgBox "Dear User, to update this report multiple Excel Sheets will be openened and closed. This may take a while."
Dim MyFolder As String
Dim MyFile As String
MyFolder = (ThisWorkbook.Path & "\Monthly")
MyFile = Dir(MyFolder & "\*.xlsx")
    Do While MyFile <> ""
        Workbooks.Open Filename:=MyFolder & "\" & MyFile
        MyFile = Dir
    Loop
            Windows("Final Report - All Months - Test - Kopie.xlsm").Activate
            ActiveWorkbook.RefreshAll
    
Dim WBs As Workbook
    For Each WBs In Application.Workbooks
    If Not WBs.Name = ThisWorkbook.Name Then WBs.Close (False)
    Next WBs
    
    Windows("Final Report - All Months - Test - Kopie.xlsm").Activate
    MsgBox "Thank you for your patience"


End Sub