title: Pull data from a specified row in Excel spreadsheet
body: <p>I'm working on a PS script to take a row of data from an Excel spreadsheet and populate that data in certain places in a Word document. To elaborate, we have a contract tracking MASTER worksheet that among other things contains data such as name of firm, address, services, contact name. Additionally, we have another TASK worksheet in the same workbook that tracks information such as project owner, project name, contract number, task agree number.</p>
  
  <p>I'm writing a script that does the following:</p>
  
  <p>Ask the user through a message box what kind of contract is being written ("Master", or "Task")
  Opens the workbook with the appropriate worksheet opened ("Master" tab or "Task" tab)
  Asks the user through a VB InputBox from which Excel row of data they want to use to populate the Word contract
  Extracts that row of data from Excel
  Outputs certain portions of that row of data to certain location in a Word document
  Saves the Word document
  Opens the Word document so the user can continue editing it</p>
  
  <p>My question is this - using something like PSExcel, how do I extract that row of data out to variables that can be placed in a Word document. For reference, in case you're going to reply with a snippet of code, here are what the variables are defined as for the Excel portion my script:</p>
  
  <pre><code>
  $Filepath = "C:\temp\ContractScript\Subconsultant Information Spreadsheet.xlsx"
  $Excel = New-Object -ComObject Excel.Application
  $Workbook = $Excel.Workbooks.Open($Filepath)
  $Worksheet = $Workbook.sheets.item($AgreementType)
  $Excel.Visible = $true
  
  #Choosing which row of data
  
  [int]$RowNumber = [Microsoft.VisualBasic.Interaction]::InputBox("Enter the row of data from $AgreementType worksheet you wish to use", "Row")
  </code></pre>
  
  <p>Additionally, the first row of data in the excel worksheets are the column headings, in case it matters.</p>
  
  <p>I've gotten this far so far:</p>
  
  <pre><code>import-module psexcel
  $Consultant = new-object System.Collections.Arraylist
  foreach ($data in (Import-XLSX -path $Filepath -Sheet $AgreementType -RowStart $RowNumber))
      {
          $Consultant.add($data)'
  </code></pre>
  
  <p>But I'm currently stuck because I can't figure out how to reference the data being added to $consultant.$data.  Somehow I need to read in the column headings first so the $data variable can be defined in some way, so when I add the variable $consultant.Address in Word it finds it. Right now I think the variable name is going to end up "$Consultant.1402 S Broadway" which obviously won't work.</p>
  
  <p>Thanks for any help. I'm fairly new to powershell scripting, so anything is much appreciated.</p>