At the end of my wits - Exporting data to MS Word 2007 form fields.

This topic contains 4 replies, has 2 voices, and was last updated by Profile photo of Norman McGregor Norman McGregor 2 years, 3 months ago.

  • Author
    Posts
  • #18072
    Profile photo of Norman McGregor
    Norman McGregor
    Participant

    Hey guys,

    I am currently having a problem that I cannot for the life of me figure out, Also I am extremely new to powershell, Watch the MVAs and I am currently on my first script.
    The problem seems simple.. but isn't at least for me! so as my final try and I am going to reach out to you guys

    Essentially I have written what was taught in the MVAs, Very basic reports for servers.
    Currently I have it set up to get the Ram usage, Service status, CPU usage, Harddisk space and all that super basic stuff.
    Its going to be doing alot more then that, But for now I want to make sure everything is working before moving on from the most basic of tasks.

    I have all the results put into a log.txt file.

    What I want to do is have it run, then export the results to to Word file, Now the thing is I have to use a Word document that has form fields I want to intelligently update those fields automatically and have it save.
    I cannot for the life of me figure out how I can use PS to export the data to the .doc form fields, From my research I could use excel and a VB script, But is there anyway PS can do this?

    Thanks in advance!

  • #18088
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    Yes, I just wrote a script to do this. First step is to ensure you have bookmark names updated in the template so that you intelligently identify the fields. You should be able to run this and it should enumerate all of the bookmarks:

    $document = "C:\Test\Template.docx"
    $Word = New-Object -Com Word.Application
    $Word.Visible = $true
            
    $doc=$word.Documents.Open( $document )
    $doc.Bookmarks | Select Name, @{Label="Type";Expression={$doc.ActiveWindow.Document.FormFields.Item($_.Name).Type}}
    #While the document is open by Powershell, you can go in here and run queries and commands
    #against the document. For instance, if you have a dropdown and you want to see how the indexes are setup,
    #you can run this command in the ISE prompt:
    #$doc.ActiveWindow.Document.FormFields.Item("myDropdownBookmark").Dropdown.ListEntries
    
    #$doc.Close() #close the document
    #$word.Quit()

    You should see all of the named bookmarks that you can now update by property name. I did not create the original template, so the bookmarks were named Text1, Text2, Text3..., so not really useful. So, in my example, let say my bookmarks were renamed to FirstName, LastName, etc. like the hash table below. In my template, I had Checkboxes, Dropdowns and text fields, so the script looks up each property to get the type and then sets the appropriate property.

    $hashTable = @{
        FirstName = "Sam"
        LastName = "Simpson";
        Address1 = "123 Nowhere Ave";
        Address2 = "";
        CityStZip = "New York, NY 12345";
        Site = 3; #Dropdowns take a index #
        Active = "No" ;#Checkboxes are 0 disabled and 1 enabled, but I converted Yes and No below
        ContactNum = "951-555-1234";
        EmerContactNum = "951-555-6432";
        
    }
    $document = "C:\Test\Template.docx"
    $Word = New-Object -Com Word.Application
    $Word.Visible = $false
            
    $doc=$word.Documents.Open( $document )
    #Verify that Word did open the template
    if (!($doc -eq $null)) {
        $doc.ActiveWindow.View.Type = 1
        Sleep -Seconds 3
    
        $hashTable.GetEnumerator() | Where{!([string]::IsNullOrEmpty($_.Value))} | foreach{
            $Property = $_.Key
            $Value = $_.Value
        
            $type = $doc.ActiveWindow.Document.FormFields.Item($Property).Type
            switch ( $type ) {
                83 {
                    "Processing as DropDown ({0}): {1}" -f $type, $Property
                    $doc.ActiveWindow.Document.FormFields.Item($Property).DropDown.Value = $Value
                }
                71 {
                    "Processing as CheckBox ({0}): {1}" -f $type, $Property
                    switch ($Value.ToUpper()) {
                        "YES" {$Checked = 1}
                        "NO" {$Checked = 0}   
                    }
                    $doc.ActiveWindow.Document.FormFields.Item($Property).Checkbox.Value = $Value
                }
                70 {
                    "Processing as Text ({0}): {1}" -f $type, $Property
                    $doc.ActiveWindow.Document.FormFields.Item($Property).Result = $Value
                }
                default{
                    "Unable to process, UNKNOWN type ({0}): {1}" -f $type, $Property
                }
            }
        }
    
        $doc.saveas([ref] $saveAs)
        #$doc.Save()
        $doc.Close()
    }#if the document opened
    else {
        "Word could not open document: {0}" -f $document
    }
    
    "Calling .Quit() method"
    $Word.Quit()
    "Calling [System.Runtime.Interopservices.Marshal]::ReleaseComObject($word)"
    
    if([System.Runtime.Interopservices.Marshal]::IsCOMObject($word)) {
        [System.Runtime.Interopservices.Marshal]::ReleaseComObject($word)
    }
    "Completed Marshall call"
    "Removing variable"
    Remove-Variable word -Force

    This should get you like 90% of the way and it took me a couple days searching google and exploring Word to find the appropriate properties to update.

  • #18096
    Profile photo of Norman McGregor
    Norman McGregor
    Participant

    First off, Thank you so much!

    This is before my first coffee look at the script,

    But it looks like the hashtable is full of bookmark names, But I am thinking I could make this a function and have the values of the hash table be the output from my function?

    So if do FirstName = "$name" it would work would it not?

  • #18108
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    You could certainly make it into a function. In my script, all it was doing was generating the variables for the hash table and running Word code. The code is called one time for each time I run the script and it's not something I think I would use over and over, so I didn't bother creating a function.

    Doing the FirstName=($name); is how my script was setup, I just made it plain text to show you the example, so it would work.

  • #18116
    Profile photo of Norman McGregor
    Norman McGregor
    Participant

    Thanks so much man,

    Also I was able to figure out how to make a hashtable completely dynamic just so you know!

    I made an object then I did the following.

    $Hashable @{
    ($object.computername + "cpu") = "$cpu"

    That makes it so the bookmarks it tries to find can be dynamically generated and same with the values.

    😀 thanks alot man, This enabled me to learn alot!

You must be logged in to reply to this topic.