Mail merge partagé : ONEDRIVE (Excel BDD, Doc Word)
Stephfun
Posted messages
11
Status
Member
-
Stephfun Posted messages 11 Status Member -
Stephfun Posted messages 11 Status Member -
Hello everyone,
I have an Excel database (DB) that I use for mail merge (via a UserForm) on different Word template documents (in a DWT folder). Everything works very well.
I would like my work to be usable by multiple users to populate the database, which is why I placed it on a shared OneDrive. I encountered a first problem when declaring the path to the template documents: the specificity of the user's identifier X is C:\Users\X\OneDrivePath\DWT
I managed to resolve the problem: by clicking a button, any user can access the DWT synchronized on their desktop.
UNFORTUNATELY, the problem persists when accessing a template document TYPE.doc. An error indicates that the path to find the database C:\Users\Xinitial\OneDrivePath\BDD is not known. Here, Xinitial is the identifier of the last user who saved their database for recipient selection. Thus, tedious manipulations ensue: modifying the properties of the data links (which seems to work one time out of three) then providing the new database, etc.
Indeed, I would like the selection of recipients in Word to happen automatically, without anyone having to do it. In short, by pressing the button of the UserForm to access the DWT, the user clicks on TYPE.doc and then directly accesses the merged document.
To do this, I thought of directly recording a macro on the TYPE.doc Word document:
In short, upon opening the document, the path to the BDD database is automatically declared (recipient selection)
UNFORTUNATELY, this does not work. Indeed, from the UserForm, when accessing the DWT and then selecting our TYPE.doc document, it does not really open. It first wants to select the fields and find the database (which we then have to locate manually). This is where I am stuck; I no longer know where to look.
Do you have any ideas or suggestions to guide me, please?
Goal: Select recipients only once and then never again by any other user => directly generate a WORD document via the Excel UserForm.
Thank you in advance for the valuable help you will provide.
Stephfun
I have an Excel database (DB) that I use for mail merge (via a UserForm) on different Word template documents (in a DWT folder). Everything works very well.
I would like my work to be usable by multiple users to populate the database, which is why I placed it on a shared OneDrive. I encountered a first problem when declaring the path to the template documents: the specificity of the user's identifier X is C:\Users\X\OneDrivePath\DWT
I managed to resolve the problem: by clicking a button, any user can access the DWT synchronized on their desktop.
UNFORTUNATELY, the problem persists when accessing a template document TYPE.doc. An error indicates that the path to find the database C:\Users\Xinitial\OneDrivePath\BDD is not known. Here, Xinitial is the identifier of the last user who saved their database for recipient selection. Thus, tedious manipulations ensue: modifying the properties of the data links (which seems to work one time out of three) then providing the new database, etc.
Indeed, I would like the selection of recipients in Word to happen automatically, without anyone having to do it. In short, by pressing the button of the UserForm to access the DWT, the user clicks on TYPE.doc and then directly accesses the merged document.
To do this, I thought of directly recording a macro on the TYPE.doc Word document:
Sub Document_Open()
Dim xuser As String
Dim src As Variant
Set oWSHShell = CreateObject("WScript.Shell")
Cheminbureau = oWSHShell.SpecialFolders("Desktop")
xuser = UCase(Split(Cheminbureau, "\")(2)) 'Retrieve user X
src = "C:\Users\" & xuser & "OneDrivePath\BDD"
ActiveDocument.MailMerge.OpenDataSource Name:= _ 'Recorded macro
"C:\Users\" & xuser & "OneDrivePath\BDD" _
, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:= _
"Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=src;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLED" _
, SQLStatement:="SELECT * FROM `Sheet1$`", SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess
End Sub
In short, upon opening the document, the path to the BDD database is automatically declared (recipient selection)
UNFORTUNATELY, this does not work. Indeed, from the UserForm, when accessing the DWT and then selecting our TYPE.doc document, it does not really open. It first wants to select the fields and find the database (which we then have to locate manually). This is where I am stuck; I no longer know where to look.
Do you have any ideas or suggestions to guide me, please?
Goal: Select recipients only once and then never again by any other user => directly generate a WORD document via the Excel UserForm.
Thank you in advance for the valuable help you will provide.
Stephfun