Update links causes file open to appear

[Mrock1]

I have a main macro enabled workbook (xlsm) that opens or adds another , creating it if it is not present (xlsx).

The main workbook macros writes data and formulas to the data workbook with the formulas referencing ranges in the main workbook.

I used the before_close event in the main workbook to save and close the data workbook.

When I next open the main workbook the vba in Workbook_Open subsequently opens the data workbook which now exists from the first session and in the same folder.

I immediately get prompted about external links with the main workbook (in the formula) not resolving even though the main workbook is of course already open. If I select to update the external links, the file open window appears and I have to select the main workbook again, which does nothing because it’s already open.

A similar thing occurs even if I open the main work book and open the data workbook manually using file /open

Why does this happen and how can I stop it yet still have the formula in the data workbook recalculate against the external links to the main workbook (remember this is already open)

According to what I’ve read, unless I update the external links (ie retrieve the formulaically reference external data from the main workbook), the data workbook will only calculate the formula with the previous data. So I suspect adding the UpdataLinks:=False will be of no use although it does suppress the error about the external links, but it just won’t retrieve the new data

hope someone can help
Thanks

 

[GWteB]

This behavior of Excel most likely means the location of the main workbook cannot be determined. You can check this yourself by opening the data workbook without (!!) having the main workbook open. At the warning, choose Don't Update. Choose a cell containing a formula with a reference to the main workbook, visible as #REF. If the reference to the main workbook looks something like this ...

'[MainWorkbook.xlsm]Sheet1'!$A$1

then the above mentioned possible cause of your issue has been confirmed: the full path to the file is missing. The moment you compose a formula and include a reference to another workbook in it, you're supposed to include a full path, like e.g.:

'C:\Users\Mrock1\Documents\[MainWorkbook.xlsm]Sheet1'!$A$1

 

[Mrock1]

Aha! I’ll give that a go tomorrow. Easy enough done. I have the full path and file name in a variable. Thanks for the info and I’ll let you know how I get on.

 

[GWteB]

You are welcome and thanks for the follow-up. I'll keep watching ...

 

[Mrock1]

Update:
When I open the data workbook on its own and choose not to update the external links the (1) cell containing the formula that references the main workbook has the fully qualified path to the main workbook in all occurrences of the reference. E.g
'C:\Users\username\Documents\mainworkbook\mainworkbook.xlsm'!namedrange

If I open the main workbook, allowing its vba code to open the data workbook (command: Set DataWkBook = Workbooks.Open(RecordsName)) and also choose not to update the links the external references in the formula are shown (as I'd expect) as:
mainworkbook!namedrange
If I open the main workbook, allow the vba code to open the data workbook AND choose to update the links I get prompted with:
"We can't update some of the links in your workbook right now...."
If I continue, I get no further messages, but I assume the values in the data workbook are not updated.
If I choose Edit Links... I see the there is one link to my main workbook of which the status reads "Source is open" and I can choose "Update Values and it seems to work. I can also choose "Open Source", which seems to reload the main workbook (it reruns Workbook_Open) and continues.

So empirically it seems that when the data workbook is opened in the Workbook_Open macro of the main workbook, the data workbook seems to think that the main workbook is not open, even though it obviously is.

I don't know if the complexity of my formula has anything to do with this problem, but the formula is essentially:
=IF(ISNA(VLOOKUP(C2,mainworkbook.xlsm!TaskList,3,FALSE)),"",IF(VLOOKUP(C2,mainworkbook.xlsm!TaskList,3,FALSE)>0,VLOOKUP(C2,mainworkbook.xlsm!TaskList,3,FALSE)-SUMIF(WBSs,C2,Durations),""))

WBSs and Durations are both named ranges in the data workbook and do exist.

This formula current exists in only one cell in the data workbook and I can tell you that deleting this formula from that cell eliminates the problem. However, as the data in the data workbook grows, the same formula will be replicated down the column. The formula's intent is to get a task name from C2, use the vlookup to get hours allocated to that task from the main workbook, then to calculate the remaining time by summing (in the data workbook all the hours recorded against that particular task and subtract that sum from the hours allocated to return the remaining hours. The IF conditions are necessary as not all tasks may have a maximum number of hours recorded against them in the TaskList range.

This all works fine if the data is in a sheet in the main workbook (and has done for years), but I'm trying to separate the data from the main workbook, so I can update the main workbook without having to copy/import the data from the old version.

Any further help you, or anyone else can offer would be greatly appreciated. I'm no newbie to Excel VBA and am quite proficient, but this is the first time I have done something like this (application in one workbook and data in another). If it were simply straight data in the data workbook, things would be very easy. However, the formula is required to dynamically calculate the hours remaining on a task and the data table in the data workbook is used to generate a pivot table to summarise the effort on all tasks over a period. There are other columns in the data table such as start and finish times, the duration of that entry etc. FYI I'm on Office 365. The data I am currently trying to separate in to the data workbook is the times recorded against tasks. I also currently have task definition related data also stored in the main workbook, but once I can figure out what is going wrong with this, I will move that task data to the data workbook as well.

 

[Mrock1]

Further Update:
I've put back the UpdateLinks:=False in the Workbook.Open(datafilename.False) to avoid the error and added an Application.Calculate immediately after to force the external referenced formula cells to recalculate, which doesn't manifest any errors an that seems to have worked properly. So maybe the update external links is unnecessary in this situation.

It's all a bit strange and I am still a bit concerned that something isn't working properly, quietly in the background, but time will tell.

 

[GWteB]

It's quite weird indeed. I don't see anything out of the ordinary in your formula, and I'm sorry to say but I haven't been able to reproduce your problem. Maybe there's a (hidden) name somewhere with an invalid reference that you're not aware of. Code below makes al hidden names visible within Name Manager. If you ever find out what in the end caused this issue please let us know.

Public Sub UnhideNames()
    Dim s As Object
    For Each s In ActiveWorkbook.Names
        s.Visible = True
    Next
End Sub

 

[Mrock1]

I’ll try that. It’s possI left by the work oil was made manually mostly. It entered the heading but allow the main workbook macro write the record values including the formula. Having. Said that tbe macros do redefine certain named ranges so maybe one of those is a bit weird. The formula refers to ranges on both the data and main workbooks. So if one of these is a. But weird accidentally referencing the main workbook (ie named range in data the refers to a sheet and cell range in main. Just maybe. Remember I am trying to separate the workbooks and create the new data workbook. But maybe my existing code hasn’t been modified properly. I’ll investigate that possibility.
Of course if I delete the cell containing the formula th s ranges aren’t reference and therefore no external reference issue - maybe.