Excel"s fullname property with OneDrive

Excel's Fullname Property with OneDrive: Solving the "https" Dilemma 📚💻🔗

So, you've saved an Excel file to your beloved OneDrive, but when you try to retrieve its full name using the open Workbook object, you encounter a little hiccup. Instead of receiving a local file path, you get a dreaded "https" address, which causes other programs to scratch their heads in confusion. 😱

But fret not! In this blog post, we'll dive into this common issue and provide you with easy solutions to get the local filename you crave. Let's get started and banish those "https" blues! 🙌

Understanding the Problem 🔍

When you save a file to your OneDrive and it undergoes synchronization, the file's properties change, including the FullName property. Instead of returning the traditional local file path, it now presents a web URL starting with "https". 🌐

This URL format poses a problem for other programs that can't interpret it, leaving you with a headache and an unfulfilled desire for the local filename. 😓

Solution 1: Using the Scripting.FileSystemObject 📂🔍

One simple solution involves leveraging the power of the Scripting.FileSystemObject in VBA. By utilizing this handy object, we can retrieve the local file path even after synchronization with OneDrive. 🚀

Here's a snippet of code to achieve this:

Sub GetLocalFileName()
    Dim objFSO As Object
    Dim strFullName As String
    Dim strLocalFileName As String
    
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    
    ' Prompt the user to select the file
    strFullName = Application.GetOpenFilename()
    
    ' Use the FileSystemObject to get the local file name
    strLocalFileName = objFSO.GetFile(strFullName).ShortPath
    
    ' Print the local file name in the Immediate Window
    Debug.Print strLocalFileName
End Sub

By utilizing the ShortPath property of the File object returned by GetFile, we can obtain the local filename even when dealing with OneDrive-synchronized files. 🎉

Solution 2: Mapping OneDrive as a Network Drive 🗺️🖥️

Another alternative is to map your OneDrive folder as a network drive on your computer. This process allows you to access the files using traditional file paths and avoids the "https" confusion altogether. 🌈

To map OneDrive as a network drive, follow these steps:

1. Open File Explorer on your computer.

2. Click on "This PC" or "My Computer."

3. Select the "Map network drive" option from the toolbar.

4. Specify the drive letter you want to assign to OneDrive.

5. In the "Folder" field, enter the URL of your OneDrive folder (e.g., https://onedrive.live.com/...).

6. Check the "Connect using different credentials" box if required.

7. Click "Finish" and follow any additional prompts to complete the mapping process.

Voila! 🎊 You can now access your synchronized OneDrive files using their local file paths through the network drive you just created.

Call-to-Action: Join the Conversation! 💬📣

We hope this article helped you tackle the notorious "https" issue when retrieving the local filename of a OneDrive-synchronized Excel file. Now it's your turn to share your thoughts and experiences!

👉 Have you faced this problem before? How did you solve it? 👉 Do you have any alternative solutions or tips to add? 👉 Is there a particular Excel topic or problem you'd like us to cover next?

Join the conversation below and let's make Excel with OneDrive more enjoyable for everyone! 🚀📈

Happy Excel-ing! 😄✨