Local links

[sammary]

I'm up to date on all my updates and I'm using Office365 on Windows 11. I'm using OneDrive and I have all files downloaded onto my hard drive.

When I create a link in an Excel file the link appears to be connected to the OneDrive version of the file rather than the local version. How can I force the link to refer to the local drive?

The work around that I'm using is to place my computer in airplane mode, create the link, close the file, go on-line and open the file. It works but it is sill to have to do this.

I'm getting a similar problem when I open an Excel file, it takes a while. A green bar at the bottom of the screen during the open process appears to be trying to open the file from OneDrive and not the local drive.

Thanks

[#2]

Well, that is how OneDrive works; you link to a file on OneDrive and Excel has a strong preference to refer to the on-line version rather than the one on your system. I don't think there is much you can do about this.

[#3]

As I recall good programming practices is to minimize network references for the sake of efficiencies. When the local drive and the network drive contain the same information, then the logical choice should be to go to the local drive.

[#4]

Unfortunately that isn't how things work 🙂
The choice for using https links starts to make sense if you consider the CoAuthoring experience: changes made to the file must be sent to the server so that other end-points are notified and other users see your changes. That is the probably the reason for the preference for the https addresses over the local folder address.

[#5]

Performance has always been read local, write remote (this is what we also did when I managed trading systems). With network speeds being what they are the write is almost immediate which should satisfy Coauthoring.
The dark side of reading from OneDrive, at least for me, is that there are times when it has taken 10-20 seconds to open a Word or Excel file. This is not my network being slow because I don't see this performance degradation with any other application.

[#6]

This morning at 0530, with my whole family sleeping (i.e. nobody was using bandwidth) it too approximately 58 seconds to open an Excel file that is 52K

[#7]

Does the Excel file contain any external links? (make sure you check range names and hidden range names too)

[#8]

The answer to your question is no and it happens to other Excel files as well

[#9]

Perhaps reinstalling the OneDrive app might help?

[#10]

@sammary 

I'd try to delete Office Cache, instruction is here How to clear office cache data without going through Microsoft Upload - Microsoft Community

[#11]

Sergei,
Thank you for this suggestion. I deleted the cache and changed the cache setting to 7 days from 14. One Excel file opened as expected and another took a bit longer (57K) but a whole lot faster than in the past. I hope this is the solution but I want to give it a couple of days.

[#12]

Jan, thanks for the response. I actually tried this and there was no effect.

[#13]

@sammary 

Ok, let see.

Another reason I read somewhere about, that's a huge number of the files in the root OneDrive folder. We speak about the thousands. They are filtered first by extension, thus if you have mainly Excel file, other apps work faster. If to move into subfolders performance on opening is better. 

[#14]

Sergi/Jan,
I have about 45 files/folders in my OneDrive folder so that is not the problem. I also cleared the cache file and while it does seem to speed the opening, I'm not sure this is the answer either.
Here is some more information that might be helpful, that I should have shared when I first opened this request for help.

There are 2 tables as I described in the initial request for help, both tables are the same, though one has about 15 rows and the other has just 1 row of data. I then use the following formula to combine the tables to create a summary. Here is the formula that I use
=LET(
stck, SORT(VSTACK(Sam,Mary),1,1,FALSE),
data, DROP(stck,,-1),
dc, TAKE(data,,-1),
rt, SCAN(0,dc,LAMBDA(a,b,a+b)),
result, HSTACK(data,rt),
result
)
The first time I open the file is when it takes the longest to open. Subsequent time are very slow but faster than the initial opening.

[#15]

Here's an interesting discovery I made concerning the issue of long durations for opening an Excel file.
If I open an Explorer window and go to the folder that contains the file and double click on it, it open almost immediately, as you would expect.

If you open Excel and double click on the file in the Recent menu this is where the delay occurs. Given the file is probably in the cache it open faster but there is a few second delay.

Thoughts?

[#16]

Have you got the preview pane visible in Explorer? I seem to recall that pre-opens the file in Excel. I always have that turned off for that reason.

[#17]

Jan,
Not sure I understand. Opening the file with the Explorer is faster than opening the file in the Excel Recent menu. In any event I don't have it turned on as I like to see