06-05-2019 06:23 AM
06-05-2019 06:23 AM
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?
06-06-2019 06:53 PM
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!
06-07-2019 03:43 AM
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?
06-08-2019 09:14 AM
@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