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

Paigan

New Member
Joined
Jun 10, 2014
Messages
12
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:

Code:
=(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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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:

Code:
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
 
Upvote 0
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:
Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top