Excel to Word - Remove Pages where bookmarks are not used

[TBR]

Hello,

Context: I have set up a macro on excel that creates a word file using a word template file dotx. The .docx created contains the Bookmarks I've set up in the Word template file.
The user of the excel file will select the content he wants to display in the word document. But the .docx that is created contains all the bookmarks even those that the user did not select in Excel. The bookmarks remain visible but are not necessary.


The .docx file created contains 50 pages and for the moment the user has to delete each page + bookmark by hand so that the word document is clean.
I have 1 bookmark per page.


Need: I would like to find a way to delete the pages containing the unused bookmarks to get a clean document without even having to manually edit the content.


After several unsuccessful attempts, I am open to any idea.
I have listed on an Excel tab the list of bookmarks and according to the user's choice. (either 0 or 1).
If 0 => Delete the page where the Bookmark is in Column A.
If 1 => OK



Bookmarks
Valid? 1/0


FS1

1


FS2
0


OVER1
1


OVER2
1


OVER3
1


OVER4
1


OVER5
0


OVER10
1


NF1
1


...
...





Sample of the code :

'Trigger dotx wordfile

With wdapp
.Visible = True
.Activate


.Documents.Add "C:\Users\" & user & "\HH\QT\testwordcopy.dotx"
Application.CutCopyMode = False




'Copy Financial Summary
Sheets("Print_Content1").Select
ActiveSheet.Range("D5:H26").Copy
.Selection.Goto wdGoToBookmark, , , "FS1"
.Selection.Paste




'Copy Overview page1
Sheets("Print_Content2").Select
ActiveSheet.Range("A2:B17").Copy
.Selection.Goto wdGoToBookmark, , , "OVER1"
.Selection.Paste

End With

'----- THIS PART BELOW IS NOT WORKING EVEN WITHOUT A CONDITIONNAL STATEMENT------
'delete the unused word page
With wDoc
.GoTo What:=wdGoToPage, Which:=wdGoToAbsolute, Name:=3 'Page number
.Bookmarks("FS2").Select
With Selection
.Delete
End With
End With



____________________

Hope it makes sense... As I said above, If you have a better idea, to add directly a vba code in my dotx file, change the way I'm transferring the datas,... I'm all ears !


Thanks a lot
Thomas

[snb]

Why does your template contain 50 pages, of which most of them are not needed ?
What does the template contain more than mere empty pages ?

Please illustrate the contents of
Sheets("Print_Content1").Range("D5:H26")
and
Sheets("Print_Content2").Range("A2:B17")

In general: never use 'select' and 'Activate' in VBA

Are you familiar with ?

Sub M_snb()
ActiveDocument.ActiveWindow.View.ShowBookmarks = False
End Sub

[TBR]

It depends on the business need. It can happen that all 50 pages are filled in as well as only 20 pages could be filled in. It is because of these variable cases that the idea of a template seemed to me the most adequate.
PFA attached the dotx file.

All the content is sensitive so I've put dummy values.
To illustrate:
Sheets("Print_Content1").Range("D5:H26") :
29715
with formulas like: =FILTER(OVERVIEW!T4:T1000,OVERVIEW!V4:V1000<>"") , it will automatically generates the rows I need in this Financial summary table.

Sheets("Print_Content2").Range("A2:B17") :
29716
It will transfer this selection into the new docx file generated.


I'll try with your suggestion and let you know "ActiveDocument.ActiveWindow.View.ShowBookmarks = False"

Thanks,
Thomas

[snb]

I would suggest to use Docvariables in the Word Document.
You can embed the docvariable in an {IF} field. So when the docvariable SF1 contains only a space, the whole page won't be printed at all.
If the Docvariable contains "1' e.g. then the whole page, including the header and the linkt to the Range in the Excelfile will be printed.

The fields in that page should look like:

{IF {Docvariable FS1}=" " "" Sommaire Financier

{LINK Excel.Sheet.12 "Excelfile.xlsx" "Print_content1!R5C4:R26C8" \a \f 5 \h \* MERGEFORMAT}

}

This means automatically that when the Word file will be 'updated', the structure of the document will be according to the values that have been assigned to the docvariables (instead of the bookmarks) in Excel.


Sub M_snb()
activedocument.variables("FS1")=1
activedocument.fields.update
End Sub

[TBR]

Thanks for your quick analysis & answer !
Unfortunately I'm not able to make it work. It is the first time I play with it. So let me rephrase & try to understand what you've suggested :

- In my dotx file, I add a Docvariable FS1 (with CTRL+F9) and I put the code you've provided & I update it with my own content.

=> LINK Excel.Sheet.12 : I'll update it with the Object "Sheet12"29717

=> "Excelfile.xlsx" : I rename it with my filename "GSS Quotation Margin Tool_beta_1.5.xlsm" => This file is a secured file, I've setup a login form (with credentials) at opening of the file. Can it be the reason why it fails?
29718

=> "Print_content1!R5C4:R26C8" \a \f 5 \h \* MERGEFORMAT} : I keep this part as is. Right?
I'm struggling to understand this last part of the code. Maybe you can enlighten me or if you have a link I can check, would be perfect ;) .

After adding it to my Word template file, I click on "F9" and I have this error message : "Error! Unknown switch argument. " (which is the same when I trigger my macro)

=> The code you've provided needs to be added in my actual Excel VBA module to replace the actual bookmark FS1 part(?)

Thanks a lot for helping me to find the best way to deal with it.
Have a nice evening.

Thomas

[snb]

The Link should look like:

{ LINK Excel.Sheet.12 G:\\OF\\__wordkoppeling.xlsb Print_content1!R5C4:R26C8 \a \f 5 \h \* MERGEFORMAT }

The best way is to have this Excel document open when opening and updating the Word file.

There are 2 ways to enter the link in the Word document:
1. You can enter this text in the Word document (ctrl-F9)
2 You can copy the desired range in the Excel document and paste it as link (see the pasting icons) in the Word document. the double \\ indicates its linking capacity.

In the attachment you will find the Word file, the linked Excel file
Save them in the same directory.

In the Excel File you will have to adapt the path in 2 macros:

Private Sub CheckBox1_Click()
With GetObject("G:\OF\Proposition financiere.docx")
.variables(CheckBox1.Caption) = IIf(CheckBox1, 1, " ")
.Fields.Update
End With
End Sub

In the Word File you will have to adapt the path in 2 Links:

{ LINK Excel.Sheet.12 G:\\OF\\__wordkoppeling.xlsb Print_content1!R5C4:R26C8 \a \f 5 \h \* MERGEFORMAT }

If you click one of the ceckboxex you can observe the result in the Word document.