Please save me from VBA- Need to create 1000 word files from SP LIst

[Anonymous]

Hi All,

I have a strange situation.

I need to create 1000 word files using data for each file from every list item in SharePoint.

I came across 2 approaches-

1) Without Premium Connector, Using Quick Parts

It failed to work for Word Online. I can't ask my users to not edit the file online

Link of that open-question on community is here https://powerusers.microsoft.com/t5/Building-Flows/Used-Quick-Parts-for-creating-1000s-of-Word-Files...

2) Using Premium Connector

This also did not work because of some internal Data Loss Prevention policy. The error on this flow is: "DLP policy restricts the use of business connector 'shared_sharepointonline' with non-business connector 'shared_wordonlinebusiness'"

Can someone please save me from learning VBA at this 11th hour. I do not want to learn that to accomplish this.

Experts kindly suggest. 

[Paulie78]

I presume that you have no control over the DLP connector? How often are you going to do this? Is it a one off or a regular thing?

[Anonymous]

It's a one off thing. The option 1 used above does work for Word Online too- but that required me to download all files, open them one by one- and save it, upload it back on SharePoint library. Can't do that for 1000 files, and I am not comfy with VBA esp. at this juncture of delivery

[Paulie78]

Trying to think of another route for you. Although it would only be about 30 mins effort in VBA. 

you could do it in Power Automate Desktop if you want to do it in a low code way.

[Anonymous]

@Paulie78 Thank you so much for helping and responding promptly, considering the plea and dire need am in. Am happy to learn either approaches, haven't used Power Automate Desktop too ever- so both would be fun to learn for me.

[DamoBird365]

Hi @Anonymous 

If this is a one off, why not just use Mail Merge with an excel data source?  https://support.microsoft.com/en-us/topic/how-to-use-the-mail-merge-feature-in-word-to-create-and-to-print-form-letters-that-use-the-data-from-an-excel-worksheet-d8709e29-c106-2348-7e38-13eecc338679

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

[Anonymous]

@DamoBird365 That's because we have 1013 files to create, I can do it and slog hours but would not learn anything out of that approach. To be efficient for future I need to learn PAD, and i tried this

I was able to create files and rename them using PAD.

However, unsure on how to input values in the document.

[PrasadAthalye]

In short, you are looking forward for Docgen capability.

Solution 1

Did you check with your Power Platform Admins, why Populate a Microsoft Word Template Action is in non business ? Inform them about requirement and they should be able to move it to business category. If not, do you have any action in Non Business category that connects to any data source like SQL or API ? You can transfer your data and use that action with Word Template and generate documents.

Solution 2

Do you have dynamics instance OR a Dataverse database created? If yes, create a dataverse table, import all data and leverage docgen capability. 

https://www.youtube.com/embed/yRTdd4ORVnI

[Anonymous]

@DamoBird365 @PrasadAthalye @Paulie78 @abm 

Great suggestions by all of you awesome experts. I wanted to share my learning on this
To create 1000 word files based on a template and values from either an excel or SharePoint list, I tried following approaches

1) Insert Merge field connected with Excel table and use Mail Merge within Word (Drawback: Files created lost bookmark links for sections within)

2) Quick Parts Method in Power Automate, does not require use of Premium Connector (Drawback: Files created had to be reopened, edited, undo-edit, Saved, before uploading on SharePoint- now the file works for open by browser view as well as App)

3) Power Automate action "Populate a word document" is a premium connector (Drawback: Showed DLP policy in my case, could be incorrect configuration by IT here but I was not able to use it)

4) Power Automate Desktop (Drawback: It's not very intuitive for a beginner like me to induce the loop using Desktop Recorder actions)

2nd Approach helped me complete the task on time. It wasn't the best- but had time ticking and skill constraint as well esp. with VBA, Desktop Recorder & DocGen thing suggested. But, still- thank you all.

[Anonymous · Accepted solution]

Used this to complete task- https://www.youtube.com/watch?v=_jrRAUky_2A&t=111s

[DamoBird365]

Hi @Anonymous 

I am pleased to hear you are sorted.  How did this overcome the limitation of having to download the file in order to see the quick parts as I note the demonstration you link to also shows that they will not show in Word Online but do in Word Desktop.

Thanks

Damien

[Anonymous]

@DamoBird365 I thought I would spend a lot of time downloading each file and making changes and undoing it, saving it and uploading each one, one by one back.

But, what I did realize was that I could download them all at one, make changes individually using keys not mouse, and upload them all at once.

Took me 20 mins for every 100 files- opening, editing, undoing the edit, resaving

Download from SP to local desktop & uploading them back to SP was done all at once- so just spent 2 mins each probably.

Took me 4 hours overall- but it is what it is given my knowledge atm and project deadline.