+ Reply to Thread
Results 1 to 3 of 3

Path & sharepoint

  1. #1
    Registered User
    Join Date
    10-30-2021
    Location
    Copenhagen area, Denmark
    MS-Off Ver
    365
    Posts
    13

    Path & sharepoint

    I have an Excel project with VBA macros, where I create a subfolder with a certain name in the folder containing the project xlsm file (unless the subfolder already exists). Then, I produce a host of new files that I to save in my subfolder. I use omething like FSO.CreateFolder(ThisWorkbook.path + "" + NewFolderName), and it worked great, till my company switched to syncing my Documents folder with OneDrive. Now, ThisWorkbook.path returns a string like "https://hfc-my.sharepoint.com/personal/[username]_[domain]/Documents/..." instead of "C:\Users\[username]\Documents\...". Note the URL instead of a drive specification, and slashes instead of backslashes. How do I fix this (apart from moving my project to a folder unaffected by OneDrive)?

    Actually, I have wrapped FSO.CreateFolder into this function (lifted from another forum post, and working wonderfully up until now):

    Function MyCreateFolder(path As String, Optional FSO As Object) As Boolean
    If FSO Is Nothing Then Set FSO = CreateObject("Scripting.FileSystemObject")

    If FSO.FileExists(path) Then
    MyCreateFolder = False

    ElseIf FSO.FolderExists(path) Then
    MyCreateFolder = True

    ElseIf MyCreateFolder(FSO.GetParentFolderName(path), FSO) Then
    If FSO.CreateFolder(path) Is Nothing Then
    MyCreateFolder = False
    Else
    MyCreateFolder = True
    End If

    Else
    MyCreateFolder = False
    End If

    End Function
    My problem is, the FSO.FileExists(path) and FSO.FolderExists(path) never turn true for these sharepoint paths, though the FSO.GetParentFolderName(path) works well with these paths.
    Last edited by NielsOestergaard; 09-26-2022 at 02:03 PM. Reason: Attach sample code

  2. #2
    Forum Expert
    Join Date
    07-23-2018
    Location
    UK
    MS-Off Ver
    365 32bit Win
    Posts
    1,908

    Re: Path & sharepoint

    Have you tried replacing the https: and / with "" and \ respectively?

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    10-30-2021
    Location
    Copenhagen area, Denmark
    MS-Off Ver
    365
    Posts
    13

    Re: Path & sharepoint

    Thanks for the suggestion!

    My full path is "https://hfc-my.sharepoint.com/personal/no_hfc_dk/Documents/Dokumenter/My project" (with "Documents" in both English and Danish, my system language).
    And, unfortunately, a path like "\\hfc-my.sharepoint.com\personal\no_hfc_dk\Documents\Dokumenter\My project" doesn't work.
    The equivalent classic path is actually "C:\Users\NO\OneDrive - HF-Centret Eftersl?gten\Dokumenter".

    I'm trying to build a macro that will work for others too, and actually thought I had done so by simply relying on the built-in functions speaking the same language.
    One way would be if I could make Excel return the classic file path instead of the URL of my file, whch is, after all, saved locally (byt synchronized). Another way would be if all the FSO functions could handle the URL-style paths properly.
    By the way, the Excel function =CELL("filname",A1) also returns the URL for a file synchronized to OneDrive, but the classic path for other files -- except in my Danish Excel I need to write =CELLE("filnavn";A1).

    To solve my problem, here and now, I have created a folder C:\User\NO\ZeroDrive, added it as a fast access folder in Windows Explorer, and moved my project there. Sad, though.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Path reference to sharepoint, not local file
    By befubo in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-23-2022, 04:45 AM
  2. Get SharePoint file path
    By Bill1234 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-04-2021, 09:51 PM
  3. Cannot Find Correct O365 SharePoint Path For Excel VBA
    By tkrupka in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-19-2021, 05:51 PM
  4. Find path to local synced sharepoint folder
    By rob vandebergh in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-16-2020, 01:06 PM
  5. [SOLVED] Retrieve SharePoint Document Library weburl path P1
    By hnguy71 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-22-2019, 03:36 PM
  6. open ppt from sharepoint using the File path in VBA
    By chandrashekar.shayan in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-23-2019, 02:17 AM
  7. View path on Workbook_Open fails on Sharepoint
    By Jacc in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-01-2017, 05:48 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1