Cell("Filename") returns URL rather than filename

BrianGGG

Board Regular
Joined
Mar 5, 2016
Messages
62
Hello. I have had the below formula in a spreadsheet for quite some time now.
The purpose of this formula is to determine the name of the Excel file that holds the formula. A table called "t_Parameters" holds the name of a subdirectory that is appended to the end of the the directory.

So, if the file is in C:\Brian\Documents and the correct t_Parameter value is "SubDir", the function will return:


C:\Brian\Documents\SubDir


The resulting directory is then retrieved by PowerQuery to determine the location of several files to read.


Code:
=LEFT(@CELL("filename",D1),FIND("[",@CELL("filename",D1),1)-1)&VLOOKUP("SubDirectory",t_Parameter,2,FALSE)


The problem is that now (with Excel 365 Early Adopter), when the file is in my OneDrive file structure, the CELL function returns a URL rather than a filename.
Something more like:

https://xxx-my.sharepoint.com/personal/xxx/Documents/Documents/SubDir

<tbody>
</tbody>


This is definitely a problem for me because PowerQuery uses this parameter and returns an error:

The supplied Folder path must be a valid Absolute path.



So, now something that worked for several months no longer functions.

Does anyone have any idea:


  • Why the URL gets returned by CELL, and how I can stop it?
  • If there's any other way to get the absolute path of the current Excel file without having to use VBA?



thanks
BrianGGG
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
I have an idea, but not how to fix this. I bump into the same issue.

In fact it is not (I think) to do with 365 early adaptor, but with workin "in the cloud" on Sharepoint where the cell path information returns the cloud address, not the local address.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

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