Cell("Filename") returns URL rather than filename
Results 1 to 3 of 3

Thread: Cell("Filename") returns URL rather than filename
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular BrianGGG's Avatar
    Join Date
    Mar 2016
    Location
    USA
    Posts
    59
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up Cell("Filename") returns URL rather than filename

    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


    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

  2. #2
    Board Regular
    Join Date
    Nov 2008
    Location
    Netherlands
    Posts
    3,402
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cell("Filename") returns URL rather than filename

    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.
    Short Guide to Better VBA - Link: https://www.mrexcel.com/forum/showthread.php?t=712119

    Please use code tags around your code:
    [Code] Your code here... [/Code]




    Engelse lessen, persoonlijk en doelgericht. Dutch tuition tailor-made for you. https://Ennef.nl

    Wearable for people with panic attacks: sidjup https://sidjup.com

  3. #3
    Board Regular
    Join Date
    Nov 2008
    Location
    Netherlands
    Posts
    3,402
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cell("Filename") returns URL rather than filename

    see also loads of others with similar problems https://onedrive.uservoice.com/forum...anging-excel-r
    Short Guide to Better VBA - Link: https://www.mrexcel.com/forum/showthread.php?t=712119

    Please use code tags around your code:
    [Code] Your code here... [/Code]




    Engelse lessen, persoonlijk en doelgericht. Dutch tuition tailor-made for you. https://Ennef.nl

    Wearable for people with panic attacks: sidjup https://sidjup.com

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •