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?
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.
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?
@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
'value was found
RegValueExists = True
'key was not found
RegValueExists = False
Function RegValueRead(i_RegKey As String) As String
On Error Resume Next
'read value from registry
RegValueRead = myWS.RegRead(i_RegKey)
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"
If RegValueExists(strOneDriveKey) Then
strOneDriveFolder = RegValueRead(strOneDriveKey)
If strOneDriveFolder <> "" Then
MsgBox strOneDriveFolder, vbInformation, "OneDrive User Folder"
MsgBox "Folder not found", vbExclamation, "OneDrive User Folder"