Search code examples
excelvbaonedrive

Pull Full Path Name in OneDrive to a Cell

Our company uses UKG's LMS. The only way I can get reports is on the website or download CSV files. I use Excel and Power Query to pull in the CSV to transform and load it. I then add key columns for various calculations.

When sharing the report with a coworker I learned that Power Query hard codes the file path. I found a way to use named cell references and =LEFT(CELL("filename"),SEARCH("\[",CELL("filename"))) to provide the path. I then concatenate the file path with the filename from another cell to be the path. When this workbook is opened on a coworker's computer the file path dynamically changes.

We've been using Dropbox but are now required to move to OneDrive. OneDrive's local file path for XLSX files is an https address. That doesn't work in the above setup.

I found (Excel's fullname property with OneDrive) by GWD.

How do I get the function GetLocalPath(ThisWorkbook.FullName) to populate the filepath into the named cell in Excel that will then drive the Power Query transform and load?

enter image description here

I saw a reference to "Insert Function" and select "User Defined" from the category but my Excel doesn't give me that option.

Solution

  • You could create a UDF (User defined function) that you can call from your sheet, which contains a call to GetLocalPath.

    Function GetThisWBLocalPath() 
    GetThisWBLocalPath = GetLocalPath(ThisWorkbook.FullName)
End Function

    You would then enter the following into the red highlighted cell:

    = GetThisWBLocalPath