How to render Power Query results into a Word Doc - Microsoft Community
i hope correct forum i’m using several technologies here.
i have been tasked automate our weekly project status reports project server. i created custom list called status project manager type the textual portion of their report.
i use power query query project pulling data several fields in project server plus last status item in custom list. the results in excel table. i used mail merge put word document.
everything great except need status fields in rich text format (rtf) bulleted or numbered lists can used.
after changing status fields text rtf in status list found mail merge won’t use rtf.
is there other way of creating word document out of query results?
i don't have project server cannot experiment part of system, possible insert multiline plain text value @ point using piece of rtf? , if so, when retrieve datum using power query, can retain multiline plain text value in resulting excel cell (e.g. if have typed in excel alt-enter terminating each line)?
if so, during mailmerge field should come across multiline text paragraph marks @ end of each line, ought able format crete kind of bulleted/numbered list mention. main problem in case more word trying apply own autonumbering , messing have. think switch off pre-merge and, if necessary, switch on afterwards.
but guess have tried route , not found way through.
to process list in rtf(or might simpler specify 1 in html) reasonably sure need save rtf content file, insert file. can *insert* using includetext, since can't save using fields, you'd have have code. if go down route, windows word consider either using word's mailmerge events processing each record in data source (get cell content, save .rtf file, insert it, or rely on includetext insert it. or write own vba perform 1 merge per data source record, , similar processing.
the other approach can think of might work manual intervention , avoid code rtf chunks contain no rtf header material, , (somehow) nest each rtf chunk in further piece of rtf specifies word bookmark somehow relates key value in record.
example, suppose query retrieves column called id contains numeric id (1,2,3 etc.) uniquely identifies each record. suppose can retrieve column manage wrap rtf in additional piece of rtf defines bookmark bkm1, bkm2, bkm3 etc. then, if can export column text file, , surround enough rtf make complete rtf file, save "mychunks.rtf", should able insert relevant piece of rtf using nested includetext field such as
{ includetext "mychunks.rtf" "bkm{ mergefield id }" }
imo is little easier imagine if text chunks defined using html. e.g. if each record in project server contains chunk looks like
<ol><li>item 1</li><li>item 2</li></ol>
if can wrap each of in anchor tag have
<a name="bkm1"><ol><li>item 1</li><li>item 2</li></ol></a>
export column, giving file lots of <a> elements
insert
<html><body>
at beginning,
</body></html>
@ end, save file "mychunks.html" , use nested includetext such as
{ includetext "mychunks.html" "bkm{ mergefield id }" }
might worth exploring though looks pretty unwieldy @ first sight.
Office / Word / Microsoft Office Programming / Unknown/other
Comments
Post a Comment