Quote preparation into a formatted template

Bloke · Yesterday 17:11:18

I must be missing something. I’m trying to embed an Excel sheet into a Word document.

From Word: Insert > Object > Excel Worksheet opens up a blank sheet. I add a few column headings, type a few rows of data with some formulae, format it as a table so it looks pretty, add an auto generated Table Totals row and it is embedded perfectly fine in Word.

I can edit anything, right click the table, choose Add Table Row, type some data and it works fine. All changes to the data are reflected in Word, the totals auto update. Beautiful.

But. If I right click and choose Delete Table Row, the embedded data in Word leaves a blank row below the Totals row. If I delete another row from the middle of the table, I get two blank rows below the Totals row in my Word doc.

No amount of row deletion, updating fields, clearing formatting, VBA trickery, nada seems to remove it. It’s almost as if the OLE link between the two objects has an internal high water mark for the maximum used row number, like the auto increment value in MySQL. And it refuses to let go of that value.

The number of rows used in the data table is going to vary from day to day based on who the embedded information is going to be sent to. It’s for preparing quote purposes, so the line items will vary wildly when embedded in Word, which is simply used to top and tail the document with nice pictures, headers, and a slightly better layout than is possible in Excel natively.

The second conundrum is that the pictures I want to top and tail on pages 1 and 3 of the word doc are full-size and need to bleed off the page when printed to PDF. Can I do that by faffing with sections, margins, page setup and printer settings? No. Regardless of what I do, I get a white border when either Save to PDF (macOS, btw) or Print to PDF options are chosen.

I’ve Googled and even consumed AI suggestions on how to combat these two issues. The best it can come up with for the phantom rows is that it “might happen”! No solution as yet.

Has anybody any nuggets of wisdom on how to either get rid of the phantom rows, print to the edge of a PDF, or does anyone have any other solutions I might try? All I need is to insert variable rows of cost data with totals and a few varying images into a nice looking multi-page PDF. Change the header text with client name, quote number, etc. Export to PDF. Send to client. Profit.

Part of me wonders if this is better handled with a little Txp plugin fed through a page template with hard coded header/footer content. Like, I dunno, some tabular data in an article somehow that gets split at delimiters and formatted as a table and embedded in the template.

The fly in the ointment is it needs to be usable by someone who has zero patience and very little tolerance for something that isn’t cut, paste, press print, done.

Thoughts?

Bloke · Yesterday 17:38:12

Hmmm, am I dreaming or did someone write a csv/xls kind of plugin that can interpret comma separated data in an article body? Or could handle uploading a csv/spreadsheet and inserting its contents into an article? I can’t remember but I’m sure someone wrote one. Can’t find it.

The more I think about it, the more attractive it sounds to be able to:

New Article in the Quotes section.
Title is the quote title.
Body holds the tabular data pasted in or uploaded/dragged/whatever.
Article image is the accompanying photo for page 2 of the report alongside the table.
Save it.
View the quote on the website.
Print output to PDF.

Sounds like it might work and be potentially less hassle than linking Word and Excel, fighting tools that aren’t designed for this kind of thing. Bonus: we get a history of quotes as articles that can be regenerated, or cloned + tweaked for rapid quote generation in future.

The sticking point is how to attach/paste the csv data into the article and interpret it to spit out a table that fits on the 2nd page. Hmmmm…

Or could each “row” of data be added to an article somehow? Even via a dashboard panel of some description? With a table shown, and “add row” facility? Seems a bit slower, potentially, and quite a bit more work, especially since this could do with being done quickly.

Btw, I’m aware that merging a data table into an InDesign / Affinity Publisher doc is the “correct” way to do this, but the client doesn’t have access to either of those, and I don’t relish trying to explain how to use it!

Last edited by Bloke (Yesterday 17:44:15)

jakob · Yesterday 18:58:05

A wild chain of thought there! Thanks for the interesting journey. Might etc_query do the job here?

uli · Yesterday 19:55:43

Sorry that reading that much text and trying to understand what’s really needed overwhelms my stroke plagued brain. But I’m a collector of txp plugins and saw this line:

am I dreaming or did someone write a csv/xls kind of plugin that can interpret comma separated data in an article body? Or could handle uploading a csv/spreadsheet and inserting its contents into an article?

I’ve 3 plugins and 1 tip containing csv in their filename.

# Name: yab_csv2table v0.3 (compressed)
# Type: Client side plugin
# Generating HTML table from simple csv files (with an option to mailing your row selection in conjunction with zem contact reborn)

# Name: lum_csv v0.9 (compressed)
# Type: Admin/Public side plugin
# Expose parts of your database to the outside world

# Name: jmd_csv v0.2 (with "raw to HTML"-Patch (compressed))
# Type: Admin/Client side plugin
# Batch-import articles from a CSV

… and finally mrdale’s question and final treatment tip

EDIT2: The mrdale thread seems to have brought us the jmd plugin. OK.

Last edited by uli (Yesterday 20:02:58)

Bloke · Yesterday 20:34:50

Aahhhh, yab_csv2table might be the one I’m thinking of. I’ll download it and see. Thank you for the links.

jakob · Yesterday 20:43:23

Bloke wrote #342999:

The second conundrum is that the pictures I want to top and tail on pages 1 and 3 of the word doc are full-size and need to bleed off the page when printed to PDF. Can I do that by faffing with sections, margins, page setup and printer settings? No. Regardless of what I do, I get a white border when either Save to PDF (macOS, btw) or Print to PDF options are chosen.

Not sure if this will work entirely for bleed situations but my trick for adding letterheads to word templates is to prepare the page background as a PDF (with bleed if you want), then insert it as an image to a Word doc’s header or footer. It comes in wrong to begin with but right-click and edit the image size and position settings so that:

wrap is set to “behind the text”
set the size of the image back to full size, e.g. 21.6 cm x 30.3 cm for 3 mm bleed
Make it absolutely positioned to -0.3cm from the page edge

It makes it a bit of a faff to edit the page headers and footers unless you lock the graphic, but usually you don’t touch that later (to return to the main document you view, you may find you need to switch to the header/footer tab on the ribbon to close the header via the button there because the usall method of double-clicking on the page gets you the background image).

You can then happily edit your document and the background prints out. It looks faded when working in word but prints out fine. You could conceivably also embed separate graphics in the header and the footer.

On the mac, you can use a PDF and it seems to work well but I’ve heard others say Word won’t take a PDF on windows. One windows user reported that a PDF version didn’t print well but a version with a PNG embedded in the document did. The advantage of the PDF is that your file size is smaller.

Here’s a zip with some test documents and background images illustrating the principle.

uli · Yesterday 20:46:53

Just saw I answered some other guy’s question for plugins over a decade back, where I mentioned aks_table. The download link there is dead but I’ve at least v0.2 saved to disk should you deem it more apt.

Last edited by uli (Yesterday 20:47:57)

jakob · Yesterday 20:50:23

uli, cool that you have all those txp nuggets squirrelled away in your archives!!

uli · Yesterday 20:55:24

My brain is all nuts now, but I still have them on HD, these nuts ;)

Bloke · Today 00:24:21

Oooh, aks_table sounds even more useful cos it uses copy and paste. I may have that lurking somewhere but I can’t find it right now. So would you please be able to post it here? Thank you.

uli · Today 00:29:44

# Name: aks_table v0.2 (compressed)
# Type: Admin/Client side plugin
# Insert a table in TxP article is very simple!
# Author: makss
# URL: http://textpattern.org.ua/
# Recommended load order: 5

# .....................................................................
# This is a plugin for Textpattern - http://textpattern.com/
# To install: textpattern > admin > plugins
# Paste the following text into the 'Install plugin' box:
# .....................................................................

H4sIAAAAAAAAA61WbW/bNhD+HAP5DzfNq6RZsCynTRpZUtcFKVZgXQskKAYkaUCLtMVVlgiS
apwl+e87UpITt0vRDf1gk3e8e+6Fp0cicTSJb1T8NHYqsmLOTMWHsUM+qktN5qWV91FudFFL
IzyLnRWeKrNHz+7kspHcaKZoW2gt4jDUbK0F0ZrJalzL5bghobE4iJ1PTCpeV0bai53JeGqx
otihTOWSC90dPsVYryvFpAYCNhvgFZyu3wGRmudGVIBg16D4SpTsB+sUO3lNWQu5h/h84f2i
14JXmMmC5AzSFFxCV7xy/ZvdwY5kS67w7DInZTkn+UfP3VR/qcgn5gZo3wY0W/xF/mx3cLc7
2B0smio36cK2izdkn1ilAxgisjBhADCPpVCeayF9uL0FK4pmXnJVuL5NBo2Gl+/enpyeuaZ/
HPHmNb12L9I08m+gP/u11QnM/VIyUWJZnht6H27PzuV5dTHyE6w43uQUcuUGzjCCbbUTbOP5
M7j79xzYOmdS6M/TON6ov1smG8g2GTB/j3QavCGOl8Im64JXy9R120ZjzpLk2itf4qlHpCTX
HriFC5Bm9vrcvCRKuWlmBU77HbEbP7Cwvrnj7t6GhSm6SKPZHSsVu8HtZGazG3YTnrZhsHIY
+S/OtV1C7gZu+KFdhrgYSPTp+nQ2uejdklDTLMEfWiZabrZWHaLic9fooWuB9sW9a9G5Fg9c
rXPJK6ZSthYlPiCec15h223rWvBFLbGlHFKYzADXBPK6qbTX+vlGNxr5YHvco50N+ReXn8zl
+OcXmS3bDe7t2ihf8zyDi9HwUT9zEffKm0dx+jsJNt0aFhdbeGAvcDNfmFLd6PSvmlee4wR9
veZAMt3ICmh9SpaesQrA6SYWhnaMUGH2nFpO6BhIX4uWgWIn6kizlpTJTvesMytYKaxq+vR5
7CRFlJ1YImvJTvVs5x1pWY6OArDrez8J0XIw2ElElhAoJFukXyHdBT7AKtw8NWO91k5G66uq
rAkFrPVjEpIMQvgGLFE2S149QHOyol4xQZbMgCShsHkV0+ykWa2IvMZUp12q/4nJ4YSVLNfG
yjBCGcDr4wDe1H/zsiQBvOKSvarXAbwVTBIgFTriDBCqCsZ02ygYwRF4eS2ufTSgve49eIIg
K/t9BseUY1boPIaXlD6gF02WCp78+HwaRTPQBdF2fzBTgC+KcV8r3oHSsq6W2R+1ZnESdhK0
vQLVCFFj3XVV9tW1HRiDJ+srJUhl08vr0u77gE3VeTLqjx80FgdRPehqYl522ZNSz7YI9clS
z8bjcV7jW6/SuDMW4RcmSWjde/i27rkd1RQnF6441UXqHE5+cgBvwWSYG6p1Jq0sCKVWjiZO
NtjZMQSEy44hIZwcyeeNZspyUa+mbEGaUm/pFDFt+cxs8ynQ6g162MLfR6FZYTmyl2zz9mfd
RW0dRVvSgkulzfCz+xu2xRc4REy2to+E5PRbY37lyPvT/+30ze/h0ckJ3ANuhetNLc1855Bb
mF1UXEwLMDAyOIRZ94+j9v+Y4XjdXashBkTpvvdW1NLf3hQ//Q7oYo9Mny/IdD453N9n5NnB
4XS+f7Cgk8P5HlLn3T/HCt3BnwoAAA==

uli · Today 08:34:59

v0.3 is still available from this page, thanks to Pete/kuopassa.

Textpattern CMS

Textpattern CMS support forum

#1 Yesterday 17:11:18

Quote preparation into a formatted template

#2 Yesterday 17:38:12

Re: Quote preparation into a formatted template

#3 Yesterday 18:58:05

Re: Quote preparation into a formatted template

#4 Yesterday 19:55:43

Re: Quote preparation into a formatted template

#5 Yesterday 20:34:50

Re: Quote preparation into a formatted template

#6 Yesterday 20:43:23

Re: Quote preparation into a formatted template

Bloke wrote #342999:

#7 Yesterday 20:46:53

Re: Quote preparation into a formatted template

#8 Yesterday 20:50:23

Re: Quote preparation into a formatted template

#9 Yesterday 20:55:24

Re: Quote preparation into a formatted template

#10 Today 00:24:21

Re: Quote preparation into a formatted template

#11 Today 00:29:44

Re: Quote preparation into a formatted template

#12 Today 08:34:59

Re: Quote preparation into a formatted template

Board footer