cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GauravG
Continued Contributor
Continued Contributor

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

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'"

 

GauravG_0-1624743895066.png

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
GauravG
Continued Contributor
Continued Contributor

11 REPLIES 11
Paulie78
Super User III
Super User III

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?

GauravG
Continued Contributor
Continued Contributor

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
Super User III
Super User III

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.

GauravG
Continued Contributor
Continued Contributor

@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
Community Champion
Community Champion

Hi @GauravG 

 

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...

 

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 😉

GauravG
Continued Contributor
Continued Contributor

@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

GauravG_0-1624883006519.png

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

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

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

 

 

Please Like and Mark this as Answer if it resolves your Issue.

Power Automate Community
GauravG
Continued Contributor
Continued Contributor

@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.

GauravG
Continued Contributor
Continued Contributor

DamoBird365
Community Champion
Community Champion

Hi @GauravG 

 

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

GauravG
Continued Contributor
Continued Contributor

@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. 

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Users online (1,340)