locked
Opening a word doc and allowing and SQL update RRS feed

  • Question

  • I have created a script that connects to an Access DB, gets a record, outputs it to a .csv file. The .csv file is converted to an Excel file and used to update several word documents. The script continues on to create a mail item and adds these word documents as attachments. When the documents are opened manually, a message appears that and you a required to click yes to update the fields.

    I want to be able to update the fields before I attach these documents to the email. I have set the script to open the word documents before I include them in the email, with the hope that document's fields will be updated, but they do not get updated in this manner. I am looking for a way to open the word document and force the update and then close the documents and send them via email.

    here is the section of the code:

    #this section of code connects to the specific access database
    Connect-AccessDB "C:\TEMP\test\xxx\dbtest\xxxPVRInquiry.accdb"
    
    #this section opens the database and selects the table we want to get data from
    Open-AccessRecordSet "select * From PVRINQT1"
    
    #here we converet the table and its data to PowerShell Objects => then we select the desired record number to work with
    Convert-AccessRecordSetToPSObject | Select-Object | Where-Object {$_.INQNUMB -eq "$RequestNumber"}|Export-Csv "c:\temp\test\milos\ABWS.csv" -Force -NoTypeInformation
    $data = "C:\TEMP\test\xxx\ABWS.csv"
    $imported = Import-Csv $data -Delimiter ","| select CallersFirstName, CallersLastName, CallersContactPhone, EventType, EventSize, ReservationDateRequested, ReservationTimeRequested, ReservationName, ReservationContactsFirstName,ReservationContactsLastName, ReservationContactPhone, ReservationContactFax, Inqnumb, ReservationContactEmail, SpecialInstructions
    # take csv and convert to Excel
    # xlsx file is used to populate the word document fields thru a semi mail merge
    
    ConvertCSV-ToExcel -inputfile "C:\TEMP\test\xxx\ABWS.csv" -output "ABWS.xlsx"
    
    #here now are our working variables
    
    $CallersFirstName = $imported.CallersFirstName 
    $CallersLastName = $imported.CallersLastName 
    $CallersContactPhone = $imported.CallersContactPhone 
    $EventType = $imported.EventType 
    $EventSize = $imported.EventSize 
    $ReservationDateRequested = $imported.ReservationDateRequested 
    $ReservationTimeRequested = $imported.ReservationTimeRequested 
    $ReservationName = $imported.ReservationName 
    $ReservationContactsFirstName = $imported.ReservationContactsFirstName
    $ReservationContactsLastName = $imported.ReservationContactsLastName 
    $ReservationContactPhone = $imported.ReservationContactPhone 
    $ReservationContactFax = $imported.ReservationContactFax 
    $ReservationContactEmail = $imported.ReservationContactEmail 
    $SpecialInstructions = $imported.SpecialInstructions
    $INQNUMB = $imported.inqnumb
    
    #take the date/time variable sent from access and strip the 12:00 AM 
    $ReservationDateRequested = $ReservationDateRequested.Replace(" 12:00:00 AM", " ")
    $RDST = ($ReservationDateRequested + " " + $ReservationTimeRequested)  #usable appointment start time
    #$RDET = ($ReservationTimeRequested 
    
    #create Calendar item
    $outlook = new-object -com Outlook.Application
    $calendar = $outlook.Session.GetDefaultFolder(9) # == olFolderCalendar 
    #calendar = $outlook.Session.olNS.GetSharedDefaultFolder
    $appt = $calendar.Items.Add(1) # == olAppointmentItem 
    # $appt.Start = New-Object system.datetime(2012,02,28,13,15,00) # == datetime construct (YYYY,MM,DD,HH,MM,SS) use 24hr time
    $appt.Start = $RDST
    
    $appt.Subject = "$EventType"  # == insert variable name for subject title                 
    $appt.Location = " xxx $EventType Event"	# == insert variable name for Location title			    
    $appt.Body = "$EventType reservation with a party of $EventSize 
    
    $ReservationDateRequested at $ReservationTimeRequested, 
    
    Reserved for the party named:   $ReservationName 
    
    The contact information is: 
    $ReservationContactsFirstName $ReservationContactsLastName
    
    Phone number: $ReservationContactPhone, 
    Fax: $ReservationContactFax     
    Email: $ReservationContactEmail   
    Inquiry number: $INQNUMB"
    $appt.Categories = "$EventType"     # == Set up Catagories WR Lunch, WR Dinner, PR Lunch, PR Dinner, Large Party
    $appt.Save()
    
    #Get event type, get appropriate documents, open them to assign Variable objects, prepare email and send
    if  ($EventType -eq "WineRoomLunchRequest") {
    	  $word = new-object -com word.application
    	  # make work visible
    	  $word.visible = $true
    	  #open a document
    	  $docWRDC = $word.documents.open('C:\TEMP\test\xxx\docs\EventsDocs\WineRoom\WRLunch\WRLunchContractM.docx')
    	  $docWRTP = $word.documents.open('C:\TEMP\test\xxx\docs\EventsDocs\WineRoom\WRLunch\WRLunchTermsPoliciesM.docx')
              $docWRDC.Save() 
    	  $docWRTP.Save()
    
    	  $docWRDC.Close() 
    	  $docWRTP.Close()
    
    	  $Word.Quit()
    	  
    	  $ol = New-Object -comObject Outlook.Application  
    	  $mail = $ol.CreateItem(0)  
    	  $Mail.Recipients.Add("$ReservationContactEmail")  			# ReservationContactEmail
    	  $Mail.Subject = "XXX $EventType"  
    	  $mail.Attachments.Add("C:\TEMP\test\xxx\docs\EventsDocs\WineRoom\WRLunch\WRLunchContractM.docx")
              $mail.Attachments.Add("C:\TEMP\test\xxx\docs\EventsDocs\WineRoom\WRLunch\WRLunchTermsPoliciesM.docx")
    	  $Mail.Body = "  
    	  $ReservationContactsFirstName $ReservationContactsLastName,
    
    	  Thank you for allowing xxx to host your event.


    JBP

    Monday, February 20, 2012 7:28 PM

Answers

  • I tracked down and tested teh method for dynamically updating a document.

    1. Set document to NOT authomatically updat eits links so there sois no prompt weh the document is opened.

    2. In PowerShell open, refresh linnks., save and close document.

    Example code. 

    $wrd=new-object -com Word.Application
    $doc=$wrd.Documents.Open($docfile)
    $doc.Fields.Update()
    $doc.Save()
    $doc.Close()
    $wrd.Quit()

    This will, assumoing all other settings are compatible, update all linked objects and fields.  Saving the file will save the current iserted data or spreadsheet.

    This should also work with linked queries as best I can remember.


    ¯\_(ツ)_/¯

    • Proposed as answer by Bill_Stewart Tuesday, February 21, 2012 6:57 PM
    • Marked as answer by Boe ProxMVP Friday, February 24, 2012 12:44 AM
    Monday, February 20, 2012 11:35 PM

All replies

  • I can now read your request.

    1. Documents will only update on open if they are designed to do so.  If you need a mailmerge then you need to execute the maimerge function.

    2.  Why do you need Outlook if yuo are doing a mailmerge.  A mailmerge takes a contact list and send to all contacts from Word.  Word calls Outlook.

    If you have fields in a document that need to be updated then you need to do  it through the fields collection.

    It is not clear what youare saking when you say you are crating a CSV that becomes Excel tnad then somehow magically updates Word.

    Start by asking one question about a piece of code.  Now on has time to study 100+ lines of code.


    ¯\_(ツ)_/¯

    Monday, February 20, 2012 7:38 PM
  • I just saw a couple of things.

    DOCX and XSLX cannot execute any code or do any mailmerging or auto updating.

    Yu might post you Word and Excel issues to teh Word or Excel forums.  The issue is not in POwerSHell of in script but in how you are designing your documents.


    ¯\_(ツ)_/¯

    Monday, February 20, 2012 7:43 PM
  • Besides the fact that there is too much code to go through, I find this horizontal scroll bar makes it impossible to understand the code.  There is one very long line in the code as posted.  See if you can scroll all the way to the right, and then scroll back, and see if you can remember what was on the line you just looked at.

    Grant Ward, a.k.a. Bigteddy

    What's new in Powershell 3.0 (Technet Wiki)

    Monday, February 20, 2012 7:46 PM
  • Besides the fact that there is too much code to go through, I find this horizontal scroll bar makes it impossible to understand the code.  There is one very long line in the code as posted.  See if you can scroll all the way to the right, and then scroll back, and see if you can remember what was on the line you just looked at.

    Grant Ward, a.k.a. Bigteddy

    What's new in Powershell 3.0 (Technet Wiki)

    Grant - I was able to opy and paste even with teh long line.

    A quick look at teh code tells me that allo of this can and should bedone in word.  Word can query access much more easily that moving the data from MSAccess to a CSV then to Excel tyhen to Word theb to Outlook.

    This is jsut a big Rube Goldberg.  It might even become viral if posted on YouTube.

    What I see is that the OP has a very basic understanding of all each one of the tools but little understanding of the advanced features.  Time to learn how really powerful Microsoft Office is.  What the OP is trying to do has been built into Office for years.  It doen not even require any scipting.  Just crate a mailmerge templates and a mailmerge query.

    The one bit is creating appointments.  A rethink would eliminate the mailing as an appointment notificationcan be mailed along with a custom message when it is created. Again all of this can be done from Word

    I don't have a day to analyze exactly what is going on. The code is too wordy and appears to be doing too many things that Word should be doing.

    Post to the Office forums for assistance on how to automate Word and data/mail merges.

    Post here with specific scripting questions about script and not why Word is not working as expected.

     

    Grant - I was able to copy and paste even with the long line.

    A quick look at the code tells me that allow of this can and should be  done in word.  Word can query access much more easily that moving the data from MSAccess to a CSV then to Excel then to Word then to Outlook.

    This is just a big Rube Goldberg.  It might even become viral if posted on YouTube.

    What I see is that the OP has a very basic understanding of all each one of the tools but little understanding of the advanced features.  It is time to learn how really powerful Microsoft Office is.  What the OP is trying to do has been built into Office for years.  It does not even require any scripting.  Just crate a mail merge templates and a mail merge query.

    The one bit is creating appointments.  A rethink would eliminate the mailing as an appointment notification can be mailed along with a custom message when it is created. Again all of this can be done from Word

    I don't have a day to analyze exactly what is going on. The code is too wordy and appears to be doing too many things that Word should be doing.

    Post to the Office forums for assistance on how to automate Word and data/mail merges.

    Post here with specific scripting questions about script and not why Word is not working as expected.

     


    ¯\_(ツ)_/¯

    Monday, February 20, 2012 8:04 PM
  • Thank you for taking the time. Field collections or word automation looks like what I need to explore. Word doesn't magically update, but I understand your points. When word opens the document it runs 'select * from 'ABWS$' and updates the fields in the documents. I merely want to be able to open word in powershell and have the "select * from 'ABWS$' execute at that point.

    JBP

    Monday, February 20, 2012 8:05 PM
  • Thank you for taking the time. Field collections or word automation looks like what I need to explore. Word doesn't magically update, but I understand your points. When word opens the document it runs 'select * from 'ABWS$' and updates the fields in the documents. I merely want to be able to open word in powershell and have the "select * from 'ABWS$' execute at that point.

    JBP

    When automated Word does not do anything automatically.  You will have to forsce an update if the document is updatable.  There are many ways to do this and all depend on how your document is designed.  Again this would best be asked in the Word forum.


    ¯\_(ツ)_/¯

    Monday, February 20, 2012 8:18 PM
  • I tracked down and tested teh method for dynamically updating a document.

    1. Set document to NOT authomatically updat eits links so there sois no prompt weh the document is opened.

    2. In PowerShell open, refresh linnks., save and close document.

    Example code. 

    $wrd=new-object -com Word.Application
    $doc=$wrd.Documents.Open($docfile)
    $doc.Fields.Update()
    $doc.Save()
    $doc.Close()
    $wrd.Quit()

    This will, assumoing all other settings are compatible, update all linked objects and fields.  Saving the file will save the current iserted data or spreadsheet.

    This should also work with linked queries as best I can remember.


    ¯\_(ツ)_/¯

    • Proposed as answer by Bill_Stewart Tuesday, February 21, 2012 6:57 PM
    • Marked as answer by Boe ProxMVP Friday, February 24, 2012 12:44 AM
    Monday, February 20, 2012 11:35 PM
  • Thank you.

    I am going to give it a whirl now.

    Thank you again.


    JBP

    Tuesday, February 21, 2012 4:51 PM