Word 365 attached template from Onedrive shows URL instead of path

[José Spanjaard]

In Word, using VBA in a template, I want to insert data from a text file to the document. I have saved the template and the text file in the same location. In VBA I use the statement "ActiveDocument.AttachedTemplate.Path" to find the location of the text file.

Now, my template and text file are in SharePoint, which I have synchronized to my local Onedrive: C:\Users\[myname]\OneDrive - [SharePoint site] etc.

On my own computer, this works fine. But on my customer's computer (for who I've created the template) the template gives an error, because it cannot find the text file. The problem is easily found, because when I look for the location of the template it shows the SharePoint-URL instead of the local Onedrive-location.

I have tried the following options for document creation: double click the template's name in the File Explorer and >File >New in Word.

My question: how can I make Word show the local OneDrive location of the attached template?

[#2]

Hi @José Spanjaard 

A few questions to trigger a few ideas:

1. Did you try hard coding the path rather than the dynamic one you've used?

2. The path location for your client will be different to yours, does the client have the files sync'd to their computer? This would also entail them having the right file permissions to access the document.

Have a great weekend ahead!

Cheers
Damien

[#3]

Hi@Damien_Rosario 

Thanks for your suggestions.

I just remembered that my files were copied directly from the File Explorer to a mapped OneDrive-folder instead of synching them from SharePoint. These directly copied files are working fine.

To be sure I am working with a proper environment, I completely removed OneDrive from my computer, re-installed it and synch'd again. Now, I am facing the same problem as my client (which is 'good ' 🙂 )

Then, I have checked your suggestions:

1. I have hard coded the path (both the local Onedrive-mapping as the SharePoint-URL), but the result is the same: Word cannot find or read the text file.

2. The files are indeed synch'd to the computer and I have the correct permissions

I think I need a checkbox where Word/Windows accepts the mapped OneDrive folder name … Any ideas?

Thx, José

[#4]

@José Spanjaard  You can pick up the current user's local OneDrive folder path from the registry, using code like this:

Dim myWS As Object

Function RegValueExists(i_RegKey As String) As Boolean
  On Error GoTo ErrorHandler
  'try to read the registry value
  myWS.RegRead i_RegKey
  'value was found
  RegValueExists = True
  Exit Function
  
ErrorHandler:
  'key was not found
  RegValueExists = False
End Function

Function RegValueRead(i_RegKey As String) As String
  On Error Resume Next
  'read value from registry
  RegValueRead = myWS.RegRead(i_RegKey)
End Function

Sub testOneDrive()
    Dim strOneDriveKey As String
    Dim strOneDriveFolder As String
    
    strOneDriveKey = "HKEY_CURRENT_USER\Software\Microsoft\OneDrive\UserFolder"
    
    On Error Resume Next
    'access Windows scripting
    Set myWS = CreateObject("WScript.Shell")
    If myWS Is Nothing Then
        MsgBox "Could not access scripting shell"
        Exit Sub
    End If
    
    If RegValueExists(strOneDriveKey) Then
        strOneDriveFolder = RegValueRead(strOneDriveKey)
    End If
    
    If strOneDriveFolder <> "" Then
        MsgBox strOneDriveFolder, vbInformation, "OneDrive User Folder"
    Else
        MsgBox "Folder not found", vbExclamation, "OneDrive User Folder"
    End If
End Sub