#
# Collecting Data from the Agilent U1252(A/B) in Batches of 20
# and putting the Data in Excel
#
# Do not run from Powershell IDE. This is annoying due to
# the gui-style Read-hosts. Instead run this script from a
# Powershell console for optimal comfort.
#
# Define the path to the excel file
$filepath="C:\Users\michael\Documents\2SK170GR-Messungen.xls"
# First Clean up excel & serial in case last run broke
if($excel){
$excel.quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | out-null
$excel = $Null
[GC]::Collect()
}
if($port) {
$port.close()
$port=$Null
[GC]::Collect()
}
# Now open excel (I use the mostly compatible Software Planmaker from Softmaker)
$excel = new-object -comobject PlanMaker.Application # ComObjekt erstellen
$excel.Application.visible = $true
$workbook=$excel.Workbooks.open($filepath)
# The File has a config sheet and a Data Sheet
# Excel has a slightly different Object name for sheets so replace for Excel
$data_s = $workbook.Sheets.Item("Messdaten")
$conf_s = $workbook.Sheets.Item("Config")
# The only Conf Paramter so far is the number of
# the last Batch
$Batch = [int]$conf_s.Cells.Item(2,2).Value2
# For debugging always assume first run
# $Batch=0;
$rowIndex=($Batch*20)+1
$Batch++
$i=1
# Now Config the Serial Port for the Agilent
# Attn: Mine is set on 19200 Baud! The Dongle is on COM4
$port= new-Object System.IO.Ports.SerialPort COM4,19200,None,8,one
$port.open()
# this is the "Trick". The DMM needs unix-style I/O
$port.NewLine="`n"
# Just to see all is fine. Configuration of the DMM is done
# locally by Hand. Not worth the hassle doing it remote
$port.WriteLine('*IDN?')
Write-Host $port.readline() -ForegroundColor 'Green'
# Now we get some Initial Data & Info
Write-host "Batch = $Batch"
$PreMatch = Read-Host -Prompt "Please enter PreMatch value:"
Write-Host ""
Write-Host "Commands during Measurement:"
Write-Host "(return) = measure"
Write-Host "(r) = repeat last measure"
Write-Host "(p) = Enter new PreMatch value"
Write-Host ""
# For debubgging only two rounds
#while ($i -le 2 ) {
#
# We measure always 20 in one run
while ($i -le 20 ) {
# wait for a key before Measurement
$answ = Read-Host -Prompt "Batch=$Batch Measure=$i PreMatch=$PreMatch -->"
# We want to repeat the last one
if ($answ -eq "r" -and $i -gt 1) {
$i--
continue
}
# N we switch the prematch Value
if ($answ -eq "p") {
$PreMatch = Read-Host -Prompt "Please enter new PreMatch value"
continue
}
# get Timestamp
$now = get-date -uformat "%Y%m%d %T"
# Now we retrieve the Primary and secondary Values
$port.WriteLine('FETC?')
$val1 = $port.readline() -as [double]
# In this case the secondary is the Temperature of the Agilent
$port.WriteLine('FETC? @2')
$val2 = $port.readline() -as [double]
# Finally put all in Excel
$row=$rowIndex+$i
$data_s.Cells.Item($row,1).Value2 = $Now
$data_s.Cells.Item($row,2).Value2 = ($Batch-1)*20+$i
$data_s.Cells.Item($row,3).Value2 = $Batch
$data_s.Cells.Item($row,4).Value2 = $i
$data_s.Cells.Item($row,5).Value2 = $PreMatch
# Next two might have to be changed if using Excel.
# Depends how Excel handles locales (Comma as Decimal Point?!)
$data_s.Cells.Item($row,6).Value2 = [System.Convert]::ToString($val1)
$data_s.Cells.Item($row,7).Value2 = [System.Convert]::ToString($val2)
$data_s.Cells.Item($row,8).Formula = '=SVERWEIS(D'+"$row"+';Config!A7:B26;2;0)'
$data_s.Cells.Item($row,9).Formula = "=F$row/H$row"
# Also put it on the Console for verification / Feedback
Write-Host "Temp= $val2 V=$val1"
$i++
}
# Save new Batch Value for next run
$conf_s.Cells.Item(2,2).Value2 = $Batch
# Clean up & Terminate
$workbook.SaveAs($filepath)
$excel.quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
$excel = $Null
$port.close()
$port=$Null
[GC]::Collect()