none
Populating a table in Word with PowerShell is slow RRS feed

  • Question

  • So I have a PowerShell script to help me document various server attributes. Most of the script creates tables in a Word document and populates them cell by cell. This is pretty slow and takes about 10 minutes to create a 50 page document. Some of my larger environments ended up with thousand page documents that took a day to create.

    However; a few sections in my script create Excel tables because the data just didn't lend itself to being broken up into tables that would fit in Word. With these tables I used the method I found in this blog http://powertoe.wordpress.com/2010/11/05/how-to-get-data-into-an-excel-spreadsheet-very-quickly-with-powershell-contd/ so that instead of populating the tables cell by cell you create a multi-dimensional array and populate the table in all at once. Using this method a decent size Excel table can be created by PowerShell in about 1/2 second.

    I've tried to modify the methods from the blog post to work with Word in addition to Excel but I haven't been able to get anything to work.

    Since I have it working in Excel my problem is not creating the multi-dimensional array it's how to get it into Word.

    Any tips on how to modify the short sample script at the top of the blog post to work with Word would be extremely appreciated.

    Thanks

    • Moved by Bill_Stewart Monday, September 8, 2014 9:53 PM Move to more appropriate forum
    Tuesday, August 20, 2013 4:05 PM

Answers

  • Hi,

    granted that word is not excel and that you can't populate a table via a multidimensional array. The fastest way to populate a table in Word is to use the Range.ConvertToTable method like so:

    $Word = New-Object -comobject word.application
    $Word.Visible = $true
    $Doc = $Word.Documents.Add()
    $Range = $Doc.Range()
    #text could also come from a file via import-csv
    $text=(Get-Process | select Handle,ID,Name  | ConvertTo-Csv -NoTypeInformation | Out-String) -replace '"',''
    
    $Range.Text = "$text"
    $separator=[Microsoft.Office.Interop.Word.WdTableFieldSeparator]::wdSeparateByCommas
    $table=$Range.ConvertToTable($separator)
    $table.AutoFormat([Microsoft.Office.Interop.Word.WdTableFormat]::wdTableFormatElegant)
    

    • Marked as answer by craigohler Wednesday, August 21, 2013 2:01 PM
    Wednesday, August 21, 2013 9:08 AM

All replies

  • Thanks for the link, but that's basically what I'm already doing. It has a FOR loop that cycles through the cells in the table populating them one by one. Doing it that way is too slow for my longer documents.

    The script in the blog post I linked in the original question simulates selecting the entire set of cells and populating them with a single command. Similar to copy/pasting a whole table in Excel vs. single cells.

    Any other ideas out there?

    Thanks.


    Craig Ohler

    Tuesday, August 20, 2013 9:23 PM
  • Well Word Is not Excel.

    In Word and most of Office we mostly would use templates that are preset to load data.  This makes it possible to insert a table in the "flow" and have the table automatically loaded from any external source.  Tis is the fastest method.  Alternately you could embed an Excel sheet into a Word document and use Excel methods to load it.

    There is no free lunch and Word is on a diet.


    ¯\_(ツ)_/¯

    Tuesday, August 20, 2013 9:33 PM
  • Hi,

    granted that word is not excel and that you can't populate a table via a multidimensional array. The fastest way to populate a table in Word is to use the Range.ConvertToTable method like so:

    $Word = New-Object -comobject word.application
    $Word.Visible = $true
    $Doc = $Word.Documents.Add()
    $Range = $Doc.Range()
    #text could also come from a file via import-csv
    $text=(Get-Process | select Handle,ID,Name  | ConvertTo-Csv -NoTypeInformation | Out-String) -replace '"',''
    
    $Range.Text = "$text"
    $separator=[Microsoft.Office.Interop.Word.WdTableFieldSeparator]::wdSeparateByCommas
    $table=$Range.ConvertToTable($separator)
    $table.AutoFormat([Microsoft.Office.Interop.Word.WdTableFormat]::wdTableFormatElegant)
    

    • Marked as answer by craigohler Wednesday, August 21, 2013 2:01 PM
    Wednesday, August 21, 2013 9:08 AM
  • Thanks Dirk. That's exactly what I wanted. Just did some tests and even large tables are populated almost instantly. Now my only problem is that some of my fields already contain commas, but I can deal with that.

    Thanks again.


    Craig Ohler

    Wednesday, August 21, 2013 2:01 PM
  • Hey,

    I also suffer from this very slow populating of an already existing table. There are about 30 tables in my document and powershell does a great job inserting the right text with $object.cell().range.text, but very slowly (takes some minutes ! )My problem is that I cannot create new tables as the document is rather complex and I want to put data into these existing tables. Is there any other way to speed things up ?

    Many Thanks 

    Monday, September 8, 2014 9:35 PM
  • Hey,

    I also suffer from this very slow populating of an already existing table. There are about 30 tables in my document and powershell does a great job inserting the right text with $object.cell().range.text, but very slowly (takes some minutes ! )My problem is that I cannot create new tables as the document is rather complex and I want to put data into these existing tables. Is there any other way to speed things up ?

    Many Thanks 


    No.  The issue is well known and it is due to the cumbersome COM interface.  Use VBA in Word to speed things up.

    ¯\_(ツ)_/¯

    • Proposed as answer by glykocalyx Tuesday, September 9, 2014 5:58 PM
    Monday, September 8, 2014 10:00 PM
  • Thanks a lot for your help ! Could you tell me where to find literature how to convert this script to VBA:

    $objWord = New-Object -Comobject Word.Application
    $objWord.visible = $true
    $PM_File = 'test.docx'
    $objDocument = $objWord.Documents.Open($PM_File)

    #first selecting table and then writing to the appropriate fields, repeat for each table
    #select table 1

    $LETable = $objDocument.Tables.Item(1)
    $LETable.Cell(1,2).Range.Text = "Pluto"

    $LETable = $objDocument.Tables.Item(2)
    $LETable.Cell(1,2).Range.Text = "Saturn"

    Tuesday, September 9, 2014 1:32 PM
  • There isn't any.  You have to learn VBA then it is easy.

    Start by searching for a tutorial on VBA.


    ¯\_(ツ)_/¯

    Tuesday, September 9, 2014 2:01 PM
  • Hi glykocalyx

    You might find some useful information on speeding up creation of Word tables in this article:

    http://msdn.microsoft.com/en-us/library/aa537149(v=office.11).aspx


    Cindy Meister, VSTO/Word MVP, my blog

    Wednesday, September 10, 2014 5:30 PM
    Moderator
  • Cindy has a good article.  It made me suspect that the script being used in PowerShell is done with Word visible.  If word I s not visible then the screen updates will be deferred.  Using the settings in the article will accomplish a speed increase even when word is visible.  Unfortunately the updates from automation will always be slower than from VBA or from a VSTO add-in.

    It is worth adding these lines to the PowerShell script:

    add-type -AssemblyName Microsoft.Office.Interop.Word
    $objWord = New-Object -Comobject Word.Application
     #$objWord.visible = $true
    $objWord .Options.Pagination=$false
    $objWord .ScreenUpdating=$false
    
    $PM_File = 'test.docx'
    $objDocument = $objWord.Documents.Open($PM_File)
    $objDocument .ActiveWindow.View.Type=[Microsoft.Office.Interop.Word.WdViewType]::wdNormalView
    
     #first selecting table and then writing to the appropriate fields, repeat for each table
     #select table 1
    
     $LETable = $objDocument.Tables.Item(1)
     $LETable.Cell(1,2).Range.Text = "Pluto"
    
     $LETable = $objDocument.Tables.Item(2)
     $LETable.Cell(1,2).Range.Text = "Saturn"
    Toggle the "Visible" comment to see if it makes a difference.


    ¯\_(ツ)_/¯


    • Edited by jrv Wednesday, September 10, 2014 6:29 PM
    Wednesday, September 10, 2014 6:28 PM
  • I would also note that the most efficient way to insert table data is to make it external and update the external data then refresh the document.

    ¯\_(ツ)_/¯

    Wednesday, September 10, 2014 6:31 PM