Author Posts

August 17, 2014 at 12:08 pm

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!

August 18, 2014 at 5:44 am

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.

August 18, 2014 at 7:15 am

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?

August 18, 2014 at 10:07 am

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.

August 18, 2014 at 2:22 pm

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!