locked
Cannot use Word Bookmarks once I have Bookmark and text in same line. Powershell script take forever. RRS feed

  • Question

  • All,

    I need your help with an interesting "phenomenon" that I am unable to solve.

    I'm working in the Wintel server branch and am developing a lot of Powershell scripts to ease my day.

    One of my current Powershell scripts is connecting to remote Windows servers, querying various data via WMI, and populating it to Excel and Word.

    In theory, my script is doing great. However, it doesn't seem to work on many source servers - where I execute the script from. As soon as my script start to assemble the Word file, it starts to either freeze or become tremendously slow.

    Many weeks I were reading articles, best practices, and tried a lot. I finally were able to figure out the root cause, but seem to be unable to get it resolved. I'm looking for every help.

    I am able to recreate the problem, and created a little script block to demonstrate the issue.

    Here's my problem:

    I am using Bookmarks in my Word template that my script use to populate data. As long as the Bookmark is the only element in the same line - everything works ok. As soon as I have other text in the same line as the Bookmark, the execution time drastically increases.

    For example:   

    • OK: <Bookmark>
    • Not OK:  <Bookmark> This is some text

    Even creating an empty new document does not deliver the desired results.

    #########
    # Execute First Test - this works fine. Measure-Command results are OK. 
    # Word template has just Bookmarks and nothing else in the same row
    
    		Write-Host
    		$WordApp1 = New-Object -ComObject Word.Application -ErrorAction Stop	
    		$Word1 = $WordApp1.Documents.OpenNoRepairDialog("C:\Users\VeltPX\Desktop\ScriptingGuy\OK.dot")	
    		# Milliseconds are OK
    		(Measure-Command {$Word1.Bookmarks.Item("One").Range.Text="1" }).TotalMilliseconds 
    		(Measure-Command {$Word1.Bookmarks.Item("Two").Range.Text="2" }).TotalMilliseconds 
    		(Measure-Command {$Word1.Bookmarks.Item("Three").Range.Text="3"}).TotalMilliseconds 
    		Start-Sleep -Seconds 5		# Insert 5 Seconds Break - in the real script we would just execute other commands here
    		# Milliseconds are still OK
    		(Measure-Command {$Word1.Bookmarks.Item("Four").Range.Text="4"}).TotalMilliseconds 
    		(Measure-Command {$Word1.Bookmarks.Item("Five").Range.Text="5"}).TotalMilliseconds 
    		(Measure-Command {$Word1.Bookmarks.Item("Six").Range.Text="6"}).TotalMilliseconds 
    		(Measure-Command {$Word1.Bookmarks.Item("Seven").Range.Text="7"}).TotalMilliseconds 
    		$Word1.SaveAs([ref]"C:\Users\VeltPX\Desktop\ScriptingGuy\OK.bak")
    		$Word1.Close()																	
    		$WordApp1.Quit()															
    		[Void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($WordApp1)
    		[gc]::collect() 
    		[gc]::WaitForPendingFinalizers()
    
    ########
    # Execute Second Test. Measure-Command results are Not OK. 
    # Word Template has Bookmark and text in one row
    
    		Write-Host
    		$WordApp2 = New-Object -ComObject Word.Application -ErrorAction Stop	
    		$Word2 = $WordApp2.Documents.OpenNoRepairDialog("C:\Users\VeltPX\Desktop\ScriptingGuy\NOK.dot")	
    		# Milliseconds are OK
    		(Measure-Command {$Word2.Bookmarks.Item("One").Range.Text="1" }).TotalMilliseconds 
    		(Measure-Command {$Word2.Bookmarks.Item("Two").Range.Text="2" }).TotalMilliseconds 
    		(Measure-Command {$Word2.Bookmarks.Item("Three").Range.Text="3"}).TotalMilliseconds 
    		Start-Sleep -Seconds 5		# Insert 5 Seconds Break - in the real script we would just execute other commands here
    		# Milliseconds start to drasticaly increase
    		(Measure-Command {$Word2.Bookmarks.Item("Four").Range.Text="4"}).TotalMilliseconds 
    		(Measure-Command {$Word2.Bookmarks.Item("Five").Range.Text="5"}).TotalMilliseconds 
    		(Measure-Command {$Word2.Bookmarks.Item("Six").Range.Text="6"}).TotalMilliseconds 
    		(Measure-Command {$Word2.Bookmarks.Item("Seven").Range.Text="7"}).TotalMilliseconds 
    		$Word2.SaveAs([ref]"C:\Users\VeltPX\Desktop\ScriptingGuy\NOK.bak")
    		$Word2.Close()																	
    		$WordApp2.Quit()															
    		[Void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($WordApp2)
    		[gc]::collect() 
    		[gc]::WaitForPendingFinalizers()
    
    ########

    The result is the following:

    958.3164

    333.1276

    365.2574

    310.553

    380.6998

    345.7621

    345.1371

    362.4962

    407.1185

    333.8541

    538.5475

    27433.6454    <----- LOOK HERE WHAT HAPPENED

    27674.4349    <----- LOOK HERE WHAT HAPPENED

    27665.2589    <----- LOOK HERE WHAT HAPPENED

    Basically this is what is happening across my entire script.

    As soon as I have a Bookmark with Text in one line, the script start to take very long. Many bookmarks push this until forever.

    I have ONE server, as well as my development computer, that seems to be NOT affected - everything is working as expected. But at the majority of devices I have this problem. I were unable to find a common cause.

    Please help!


    Monday, October 12, 2015 11:06 AM

Answers

  • Hi Patrick

    My recommendation would be to work with the Word Open XML file format, directly. The file format was specifically designed to enable tasks that need to run server-side to execute without the Word Application being required/present. Any tool that can work with Zip packages and XML can do this, but the Open XML SDK makes life simpler. Since I'm not familiar with PowerShell I don't know how that strategy would fit into your "life", but FWIW that's the route I'd consider... 


    Cindy Meister, Office Developer/Word MVP, <a href="http://blogs.msmvps.com/wordmeister"> my blog</a>

    Thursday, October 22, 2015 6:35 PM

All replies

  • Turn off auto features such as spell check and autocorrect and autoformat.


    \_(ツ)_/

    Monday, October 12, 2015 11:48 AM
  • Tried to disable SpellingAsYouType and GrammarAsYouType without improvement.
    Haven't found a way to explicitely disable Spellcheck, autocorrect and autoformat.

    This is what I've tried without success:

    #$WordApp = Application COM Object
    #$Word = Document Object
    $WordApp.Visible = $false
    $WordApp.Options.Pagination=$false
    $WordApp.DisplayAlerts=0 			
    $WordApp.AutomationSecurity=3		
    $WordApp.Activate()
    $WordApp.Options.CheckSpellingAsYouType = $false	
    $WordApp.Options.CheckGrammarAsYouType = $false		
    $Word.Activate()

    Monday, October 12, 2015 12:54 PM
  • Why are you using "Activate"?

    What versions of Word?

    What versions of PowerShell?

    If there are broken documents in the session they will have to be dispatched.

    Documents should be docx and templates dotx.


    \_(ツ)_/



    • Edited by jrv Monday, October 12, 2015 2:59 PM
    Monday, October 12, 2015 2:56 PM
  • If you are trying to build a table with bookmarks you should just use a table object that is data enabled then load it in one shot from a CSV.


    \_(ツ)_/

    Monday, October 12, 2015 3:00 PM
  • I run this exact code:

    $template = 'C:\test\NOK.dotx'
    $saveto='C:\test\NOK.docx'
    $WordApp2 = New-Object -ComObject Word.Application -ErrorAction Stop
    $Word2 = $WordApp2.Documents.Add($template)
    
    # Milliseconds are OK
    (Measure-Command { $Word2.Bookmarks.Item("One").Range.Text = "1" }).TotalMilliseconds
    (Measure-Command { $Word2.Bookmarks.Item("Two").Range.Text = "2" }).TotalMilliseconds
    (Measure-Command { $Word2.Bookmarks.Item("Three").Range.Text = "3" }).TotalMilliseconds
    # Insert 5 Seconds Break - in the real script we would just execute other commands here
    Start-Sleep -Seconds 5
    
    # Milliseconds start to drasticaly increase
    (Measure-Command { $Word2.Bookmarks.Item("Four").Range.Text = "4" }).TotalMilliseconds
    (Measure-Command { $Word2.Bookmarks.Item("Five").Range.Text = "5" }).TotalMilliseconds
    (Measure-Command { $Word2.Bookmarks.Item("Six").Range.Text = "6" }).TotalMilliseconds
    (Measure-Command { $Word2.Bookmarks.Item("Seven").Range.Text = "7" }).TotalMilliseconds
    
    $Word2.SaveAs($saveto)
    $Word2.Close()
    $WordApp2.Quit()
    [Void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($Word2)
    [Void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($WordApp2)

    I see no timing issues.  Later sets run as fast or faster then the early one.  PowerShell V5 on Windoows 10 with Office 2013/Online

    PS - you don't need to use GC as it will not do anything.  You do need to release all objects that you have referenced.


    \_(ツ)_/


    • Edited by jrv Monday, October 12, 2015 4:01 PM
    Monday, October 12, 2015 3:59 PM
  • Hi JRV,

    version details are:

    • Powershell: 3.0
    • Server: 2008R2 with SP1 (6.1.7601)
    • Office: Word 2010 (14.0.7015.1000) 32-bit

    I have the exact same problem on many servers but not all. I cannot determine a "root cause".

    What am I trying to do? I try to create Server Runbooks in Word with Server Inventory Data. For example: "This is the Server <Bookmark1> in the <Bookmark2> Environment. It has <Bookmark3> CPUs and a total RAM of <Bookmark4>". Not exactly like that but it will explain what I'm trying to archive.

    DOC version - no matter if I use DOC or DOCX, DOT or DOTX - always same result.

    For troubleshooting purposes I were playing around with Visibility=$true and Activate=$true and vice versa. However it doesn't matter if it's visible or not.

    One interesting observation: If it is visible, it will immediately continue once I click into the Application window (Focus). If I don't click into the application window, it will take very long or forever.

    I cannot try with PowerShell V5 on Windoows 10 with Office 2013/Online .

    Related to the source document templates: I've even created new word documents and just added bookmarks - nothing else.

    Monday, October 12, 2015 5:06 PM
  • Office applications perform very poorly with automation on  server.  The server will defer User automation if it becomes busy.  PowerShell V5 has been updated to improve this performance issue.

    If you are running 32bit Office then try and run the x86 version of PowerShell as it should perform better.

    If you set up your document as a mail merge document the performance will be greatly improved.

    Excel can create documents like word and is much better as you can feed cells by name which may be more efficient. 

    You can also use C# to create a Word Add-In which will be much more efficient.

    You can post in Word Developers Forum for more help with this.


    \_(ツ)_/

    Monday, October 12, 2015 5:21 PM
  • Unfortunately this doesn't really help. I have tried both versions, 32bit as well as 64bis, Powershell Console, ISE and PowerGUI. Always the same repeatable results.

    Again I have one server where it's working fine - as expected. But others where it doesn't. I cannot find the root cause or difference between the servers. Why it's sometimes working, sometimes not? For me this sounds more like a "bug" that requires a special Hotfix, Patch, KB or whatever to get addressed. However I was not able to find anything related to this.

    In the meanwhile I've updated Powershell to v 4.0 - on the Server where it was running ok, it still is running ok. On the other servers, where it never was running OK, it still is not running ok. Thus the Update to v 4.0 hasn't improved anything.

    Any other ideas how I could nail down my issue?

    Monday, October 19, 2015 9:22 AM
  • The problem is not with PowerShell.  It is with Office.

    What does "not working" mean anyway.  If it is slow then there is a problem with your installation as you have already proven that it works fine on other systems.


    \_(ツ)_/

    Monday, October 19, 2015 9:43 AM
  • In the meantime I made extensive testing with Office/Word. Repair installation, reset user settings, adjust security zones etc. - no success. The script still start to become terribly slow.

    I created a similar script in VBA - and the issue does NOT occur. Thus I still tend to exclude Office as root cause and focus again around Powershell or COM integration.

    It seems that only particular user profiles are affected if using COM for Word.

    Any ideas?

    PS: With "not working" I mean it will significantly slow down, with "working" I mean it will run smooth and fast as expected.

    Thanks!!

    Thursday, October 22, 2015 8:22 AM
  • Hi Patrick

    My recommendation would be to work with the Word Open XML file format, directly. The file format was specifically designed to enable tasks that need to run server-side to execute without the Word Application being required/present. Any tool that can work with Zip packages and XML can do this, but the Open XML SDK makes life simpler. Since I'm not familiar with PowerShell I don't know how that strategy would fit into your "life", but FWIW that's the route I'd consider... 


    Cindy Meister, Office Developer/Word MVP, <a href="http://blogs.msmvps.com/wordmeister"> my blog</a>

    Thursday, October 22, 2015 6:35 PM