0

This might seem a little niche but I am encountering a weird visual issue when copying a table from excel into word, exporting the file as a PDF then printing it. I am doing all of this using Visual Basic. The reason why I do it this way is because I prepare a quote in excel and within that file, there are tables that have formulas to reference certain items. Then everything is exported to word then PDFed so I can email it to my client.

This picture shows the end result as above. The tables were copied into word, word was exported into PDF, then printed.

This picture shows the end result when printed directly from word. This is the result i want, however i can't send a word document to the client, it needs to be PDFed.

In this picture, the table at the top was screen snipped (using snipping tool) and pasted in. File was converted to PDF and printed. The colours are slightly off for that table now but there are no rectangles.

Below is the code i use (in case it is relevant)

'to paste in the table, [w] variable is the word.document
    Worksheets("TABLES").Range("InsuranceTable").Select
    Selection.CopyPicture Appearance:=xlPrinter, Format:=xlPicture
    w.Bookmarks("PreconTable").Range.Paste 'page 4

'to export the word document to PDF. I have tried both with the same result
    ActiveDocument.SaveAs fPath & "\" & fName, wdFormatPDF

    ActiveDocument.ExportAsFixedFormat fPath & "\" & fName, wdExportFormatPDF, True, wdExportOptimizeForPrint

Does anyone have any idea why I am getting these boxes?

Jeremy Thompson
  • 61,933
  • 36
  • 195
  • 321
AnthonyGFS
  • 13
  • 1
  • 4
  • The conversion of Excel to PDF isn't 100%, I'd suggest playing with the Style of your Excel spreadsheet. Because you're using all in-built commands its an issue with the products. Alternatively roll your own method: https://stackoverflow.com/q/46580718/495455 – Jeremy Thompson Oct 14 '21 at 01:54
  • Maybe... that text has an unknown background setting in Excel and/or Word, that is conflicting?? Word:https://stackoverflow.com/a/65222172/3654325. Excel has (from memory) Fill, Background or Pattern... I believe in Excel that Home>arrow next to Fill Color>No Fill, removes all types. – Stax Oct 14 '21 at 03:16
  • @Stax I think you may be onto something. I looked into the text/image background thing for excel and discovered that there is some flattening that occurs with transparent images before a PDF is printed. When printing, there is a setting in advanced called "PRINT AS IMAGE" and selecting that fixed my issue. Now, i just need to know how to do that from my computer without having to tell my clients to print it as an image – AnthonyGFS Oct 14 '21 at 04:18

1 Answers1

0

I suspect that the source of the problem stems is Selection.CopyPicture

I would just copy and paste it as a table using the PasteExcelTable method

Worksheets("TABLES").Range("InsuranceTable").Copy
w.Bookmarks("PreconTable").Range.PasteExcelTable LinkedToExcel:=False, WordFormatting:=False, RTF:=False
Timothy Rylatt
  • 7,221
  • 2
  • 10
  • 14