# # 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()