Dynamic range in to word bookmark

[KVT Holland]

I've been trying to populate an excel template .dotx and saving it as .doc

I managed to get most parts of the code working. But its 4 in the morning and i cant figure out the last part;

[COLOR=#3366FF][FONT=Arial]Dim LastRow As Long


[/FONT][/COLOR][COLOR=#3366FF][FONT=Arial]LastRow = Groslijst.Range("A" & Rows.Count).End(xlUp).Row[/FONT][/COLOR]
Groslijst.Sheets("blad1").Range("A2:F" & Cells(Rows.Count, "a").End(xlUp).Row).Name = "Groslijst"


With New Word.Application
         .Visible = True
         .WindowState = wdWindowStateMaximize




With .Documents.Add(Template:="C:\Users\koenv\Desktop\Excel Biobeheer\RABP sjabloon clean coppy V2 6-11-2017.dotx")
     With .Bookmarks
            .Item("Groslijst").Range.InsertAfter Sheets("Data Sheet").Range("Groslijst").Text
     End with
End with
End With



End sub

Ive been failing to get the code i wanted to show working. But it should work as followed;

Groslijst is a dynamic range 6 columns and up to 3000 rows.
The Bookmark in Word is get "moved" by the page break.

There are 25 Bookmarks that need big ranges and 45 that need text from a single cell.
I started using only single cell named ranges thinking it shouldn't be hard to add the big tables.

I have no idea on how to add the ranges in to excel. I tried selecting the range and .copy .paste Paste special. Tables and ended up just trying everything i could find.

[COLOR=#7D2727][FONT=inherit]Call CreateTableFromString(wdDoc.Bookmarks("tblplc_" & sTableName).Range, rInputData)
'[/FONT][/COLOR][FONT=Verdana]https://stackoverflow.com/questions/19916536/automated-word-template-creation-with-vba?noredirect=1&lq=1
[/FONT]
----------------------------------------------------------------------------------------    
WordApp.Documents.Open WORDDOC
    
    ' Copies the named range "OrderRange" from the Excel book you are running this from.
    ws.Range("OrderRange").Copy
    ' Pastes it to the bookmark "OrderBookmark" in your Word doc template.
    WordApp.ActiveDocument.Bookmarks("OrderBookmark").Range.Paste
'https://www.mrexcel.com/forum/excel-questions/673873-macro-copy-paste-excel-range-into-word-doc-then-print-pdf.html

--------------------------------------------------------------
[COLOR=darkblue][FONT=Verdana]Sub[/FONT][/COLOR][FONT=Verdana] CertGenerator()[/FONT]

Sub CertGenerator()




Set Price = Sheets("Data Sheet").Range("Price")
With New Word.Application
      .Visible = True
     .WindowState = wdWindowStateMaximize 
     With .Documents.Add(Template:="C:\Administration\Documentation\Templates\Cert Template 2013.docx")
          With .Bookmarks 
               .Item("Price").Range.InsertAfter Sheets("Data Sheet").Range("Price").Text 
         End With 
         .SaveAs ThisWorkbook.Path & "" & Sheets("Data Sheet").Range("Name").Text & " Cert " & Format(Date, "yyyy") 
         '.Close 
End With 
'.Quit
End With
End Sub
[COLOR=darkblue][FONT=Verdana]
[/FONT][/COLOR]'https://www.mrexcel.com/forum/excel-questions/706988-export-excel-ranges-word-bookmarks-using-vba-specific-formatting-place.html[COLOR=darkblue][FONT=Verdana]
[/FONT][/COLOR]'https://stackoverflow.com/questions/32739715/open-word-template-paste-excel-data-in-and-save
-----------------------------------------------------------------
.Item("testbm").Range.InsertAfter Worksheets("Sheet1").Range("A1").Value

   'https://www.ozgrid.com/forum/forum/help-forums/excel-general/141223-update-word-document-from-excel-with-bookmarks-and-keep-bookmarks-with-vba
----------------------------------------------------------------
[URL]https://www.experts-exchange.com/articles/8933/How-to-quickly-and-accurately-populate-Word-documents-with-Excel-data-charts-and-images-including-Automated-Bookmark-generation.html[/URL]

-----------------------------------------------------------------
[URL]https://www.ozgrid.com/forum/forum/other-software-applications/excel-and-or-word-help/113582-excel-data-to-word-bookmarks[/URL]
---------------------------------------------------------------
[URL]https://msdn.microsoft.com/en-us/vba/excel-vba/articles/exporting-a-table-to-a-word-document[/URL]

I home you guys can explain what i don't understand about exporting tables/ranges a to a bookmark.

Sincerely,

Koen.

 

[Macropod]

Trying to insert a 6-column * 3000-row table into Word is rather excessive; Word doesn't really handle such large tables very well. In any event, you can't insert the contents of multiple Excel cells using .InsertAfter; you'd need to either copy/paste or create the table in Word and write each Excel cell's value to the corresponding Word table cell.

 

[KVT Holland]

Thanks for the insights, i'll drop the insertafter and focus on creating a table.

Word doesn't like the big tables indeed, lucky for us most of them are small. We "need" Word to create a report we can mail to our customers. I've made something similar for PDF, but they didn't like it. If there are better alternatives please let me know. It's due wednesday morning and it's a examination period, so i can't spend too much time.

*some time after forgetting to post the respond*

IT WORKED!!!!

Thanks a lot!

Private Sub CommandButton1_Click()    Dim wd As Word.Application
    Dim invoice As Word.Document
    Set wd = New Word.Application
   
    Application.Goto Reference:="Testnaam"
    Selection.Copy
  
With wd
      .Visible = True
         .WindowState = wdWindowStateMaximize
            With .Documents.Add(Template:="C:\Users\\Desktop\\sjabloon clean coppy V2 6-11-2017.docx")
                With .Bookmarks
                    .Item("test1").Range.PasteExcelTable LinkedToExcel:=False, WordFormatting:=True, RTF:=False
                    End With
            End With
       End With
End Sub

Still in the trial and error state, but it's a start.

 

[Macropod]

If you're creating reports for customers, why aren't you using mailmerge?

 

[KVT Holland]

The excel sheets are filled on site including customer data and an entire index of all climate influencing apparatus and water tap's and drains.

I have looked into the mailmerge, but it didn't really look like something that builds a report like ours. We have hundreds and even thousands
of data points for single customer. Am i mistaken? Also the report (maintenance and legionella prevention plan) is shared, mailed was a wrong
word choice.

 

[Macropod]

Without knowing what your report actually contains, one can't really say how you might configure a mailmerge.

 

[KVT Holland]

front Page with 1 large picture and 3 small pictures, Page index, 20 chapters, 20.000 words excluding tables, header/footer with 3 logo's, a couple of pictures that explain the tables, 20 bookmarks for the tables and some hyperlinks.

* The clean template version without the tables and pictures is 2500kB an excel sheets without data is 6MB and when filled its around 15MB.

 

[Macropod]

Nothing in what you've described can't be done with a mailmerge - even the tables, for which you might employ DATABASE fields. Still, since you have a working solution, you may as well stick with that.

 

[KVT Holland]

Thanks a lot. Remind me to link the full code when it's done.