andrew_valentin

Auto-Populate Excel Document - Workflow

Jump to solution

I have successfully created an 'invoice' Microsoft Word template in my SharePoint Online system. A workflow takes data from a list called "Jobs" and places the information in corresponding columns in a Document Library called "Instructions".

Once the workflow has finished not only has all of the relevant data been copied across to the columns in "Instructions" but the Document Library has also created a complete Word Document from my template - and converted the Document Property Tags into the values of the named columns needed. This is auto-creating my invoices no trouble at all.

As I work at a Building Surveyors Office we have Risk assessments to carry out for each job also. We often use applications that the Client and Contractors insist on using, so I would need to find a way to 'Auto-Populate' or 'Auto-Create' a Microsoft Excel Document.

I have faith in Nintex Workflow Technologies, infact most problems I have faced/will face during this project I am comfortable in the fact that the workflows can help me do just about anything, so my grey area is with the Excel Template.

How would I set up Document Properties in Excel? (The process used for Word is not available in Excel)

Anyone have any Ideas?

Many Thanks,

Andrew

0 Kudos
Reply
2 Replies
mapeacock
Rookie

Re: Auto-Populate Excel Document - Workflow

Jump to solution

Hi Andrew,

Please have a look at Vadim Tabakman's post below:

Nintex Workflow - Updating an Excel Spreadsheet UDA - Vadim Tabakman

See if that works for you.

Cheers,

Mark

Reply
andrew_valentin

Re: Auto-Populate Excel Document - Workflow

Jump to solution

I have since worked this out. Very similar to my Microsoft Word Document.

I create an Excel Template and attach as a "Template" to a Document Library.

I then "Create Columns" in my Library Settings and these become "Document Properties - Server".

A workflow carried data from my Jobs list into the Risk Assessment Document Library.

Not as easy as Microsoft Word's Quick Parts - Document Properties from SharePoint, I have to use Macros to draw the data from the Document Properties. As these are not actual typical Document Properties the Excel Coding I use is something like this:

"

Sub SharePointProperties()
Dim WB As Workbook
Set WB = ThisWorkbook

With WB.Sheets("Site Hazard Check list")

.Range("C8").Value = WB.ContentTypeProperties("Access on to and within site")
.Range("D8").Value = WB.ContentTypeProperties("Access on to and within site (Details)")
.Range("F5").Value = WB.ContentTypeProperties("Site Manager")

"

Once this code runs, all cells are pulling the Document Properties they've picked up from SharePoint. MY code then deletes the module so once the Contractors get this document there are no macros, no code, just data in cells.

Im sure there are other ways do this, my lazy playing about with different ideas has worked out in the end though, this is the easiest way I have seen online. I could not do the others.

Reply