1

Metcal Data Export to EXCEL

Hello,

Just wonder if I can export the data collected from the procedure into EXCEL worksheet? What code to use during procedure creating/editing for this function or gathering the data after procedure completion? 

Please advise. Thanks.

 

 

8 comments

Date Votes
1
Avatar
Mohamed Ghadi

Hi Tam

To collect data you should use this code in your procedure:

  •  1.005  VSET         MFILE = C:\Users\Public\meas.txt
  •  1.006  VSET         MFILE_FORMAT = VERBOSE
That give you a text file but you can convert it to Excel file easily.
 
Don't forget to put #:
  •   #1.005  VSET         MFILE = C:\Users\Public\meas.txt
  •   #1.006  VSET         MFILE_FORMAT = VERBOSE
 
to stop collecting data when you are running your procedure for day to day calibration.
 
Regards

 

0
Avatar
Michael Johnston

You can use the LIB FSC to interact with Excel to put data into it. It was added in v8 I believe. It's a bit complex, but there are some examples available from Fluke. I used this method for a procedure that performs a shunt verification on our pressure transducers. We use COMPASS software to calibrate the transducer, then the techs run the MET/CAL procedure for the resistance checks and it opens the Excel sheet generated by COMPASS, does the tests, and fills the readings into the same Excel sheet as it goes.

If you're looking to do this long term, the LIB FSC is the approach you'll want to use, rather than the MFILE option.

You can also export your Crystal report to an Excel format after the fact from MET/TRACK, if you're still using that. At the top left of the Crystal Report window when you generate one, there's a button that says Export report, and you can choose and xls.

Not sure about MET/TEAM, but you can export your test results window directly from MET/CAL v9 as well.

0
Avatar
Tam Tu

Mike,

Can you post few samples of the LIB FSC that open the EXCEL sheet and how it work? Thanks.

 

1
Avatar
Mohamed Ghadi
Hi Tomli
 
This is an example to write in Excel file
 
  • # Open an instance of Excel and make it visible
  •   1.001  LIB          COM excel = "Excel.Application";
  •   1.002  LIB          excel.Visible = True;
  • # Create a new empty workbook
  •   1.003  LIB          COM workbooks = excel.Workbooks;
  •   1.004  LIB          workbooks.Add();
  • # Select Sheet1 in the workbook
  •   1.005  LIB          COM worksheets = excel.Worksheets;
  •   1.006  LIB          COM worksheet = worksheets.item["Sheet1"];
  •   1.007  LIB          worksheet.Select();

 

  • # Select cell A1 in the selected worksheet for Current Date
  •   1.008  MATH         MEM2 = date("YYYY/MM/DD")
  •   1.009  MATH         S[1] = "Date : "
  •   1.010  MATH         S[1] = S[1] & MEM2
  •   1.011  MATH         S[11] = "A1"
  •   1.012  MATH         Cell = S[11]
  •   1.013  LIB          COM selectedCell = worksheet.Range[Cell];
  •   1.014  LIB          selectedCell.Select();
  •   1.015  LIB          selectedCell.FormulaR1C1 = [S1];

 

  • # Select cell A2 in the selected worksheet for your Name
  •   1.016  MEM2         Enter your Name:
  •   1.017  MATH         S[1] = "By : "
  •   1.018  MATH         S[1] = S[1] & MEM2
  •   1.019  MATH         S[11] = "A2"
  •   1.020  MATH         Cell = S[11]
  •   1.021  LIB          COM selectedCell = worksheet.Range[Cell];
  •   1.022  LIB          selectedCell.Select();
  •   1.023  LIB          selectedCell.FormulaR1C1 = [S1];

 

  • # Select cell A3 in the selected worksheet for your Project Name
  •   1.024  MEM2         Enter Title of your Project:
  •   1.025  MATH         S[1] = "Project : "
  •   1.026  MATH         S[1] =  S[1] & MEM2
  •   1.027  MATH         S[11] = "A3"
  •   1.028  MATH         Cell = S[11]
  •   1.029  LIB          COM selectedCell = worksheet.Range[Cell];
  •   1.030  LIB          selectedCell.Select();
  •   1.031  LIB          selectedCell.FormulaR1C1 = [S1];

Send me your email, I will send you an example more detailled.

Regards

0
Avatar
Michael Johnston

Here's the one I mentioned for the transducers. The ability to save the Excel sheet automatically at the end was something I had to work on.

  1.001  ASK+           K
  1.002  ASK-   R     N                                           F        V

  1.003  DISP         This procedure performs the R Cal for a pressure
  1.003  DISP         transducer. The pressure portion of the calibration
  1.003  DISP         must already be complete before performing the R Cal.

  1.004  SCPI         [@34970]*RST
  1.005  MEMI         Which 34970A Multiplexer channel is being used?
  1.006  MATH         @MuxChan = MEM

#Get UUT asset number and remove dashes
  1.007  MATH         @asset = FLD(UUT(),1,"-")&FLD(UUT(),2,"-")&FLD(UUT(),3,"-")
  1.008  IF           LEN(@asset)==8
  1.009  MATH         @asset = @asset & "0000"
  1.010  ENDIF
#Get Date in file name format
#  1.011  MATH         @date = FLD(GETV("MDY$"),3,"/")&FLD(GETV("MDY$"),1,"/")&FLD(GETV("MDY$"),2,"/")
#Get MWO number
  1.011  MATH         @MWO = GETV("WO$")
#Get iteration of dat file
  1.012  MEMI         Enter the 3-digit iteration number of the dat file
  1.012  MEMI         (the last three digits after the underscore).
  1.013  MATH         @num = MEM

##################################################
#For Test Runs
#  1.014  MATH         @MuxChan = 111
#  1.015  MATH         @asset = "NP3597740000"
#  1.016  MATH         @MWO = "921185"
#  1.017  MATH         @num = 002
#
##################################################

  1.014  MATH         @dir = "\\\\RSAC30-WB0056\\metapp\\Compass\\Data\\" & @asset &"\\"
  1.015  IF           @num >= 10
  1.016  MATH         @file = @MWO & "_" & @asset & "_0" & @num & ".xls"
  1.017  ELSEIF       @num >= 100
  1.018  MATH         @file = @MWO & "_" & @asset & "_" & @num & ".xls"
  1.019  ELSE
  1.020  MATH         @file = @MWO & "_" & @asset & "_00" & @num & ".xls"
  1.021  ENDIF
  1.022  MATH         @datafile = @dir & @file
  1.023  MATH         @savefile = @dir & @MWO & "_" & @asset & ".xls"

# Setup clean-up process
  1.024  CLEAR        -c     Sub Cleanup Excel
# Open an instance of Excel and make it visible
  1.025  LIB          COM excel = "Excel.Application";
  1.026  LIB          excel.Visible = True;
# Open the workbook
  1.027  LIB          COM workbooks = excel.Workbooks;
  1.028  LIB          workbooks.Open(@datafile);
#Create the workbook object
  1.029  LIB          COM workbook = excel.Workbooks[@file];
# Select "Positive" sheet in the workbook
  1.030  LIB          COM worksheets = excel.Worksheets;
  1.031  LIB          COM worksheet = worksheets.item["Positive"];
  1.032  LIB          worksheet.Select();

  1.033  HEAD         {Shunt Measurement Data}

  1.034  HEAD         -2{Input (+) to Output (+)}

  1.035  DISP         Connect the Resistance Decade between
  1.035  DISP         UUT Input (+) and Output (+)

  1.036  RSLT         =500 kohm
  1.037  DISP         Set the resistance decade for 500 kohm.
#  1.036  SCPI         [@34970]ROUT:CLOS (@312,311)
#  1.036  SCPI         [@34970]ROUT:OPEN (@313,314,315,316,317)
  1.038  LIB          COM selectedCell = worksheet.Range["C53"];
  1.039  LIB          selectedCell.Select();
  1.040  SCPI         [@34970]MEAS:VOLT:DC? (@[V @MuxChan])[I]
  1.041  MATH         meas = MEM * 1000
  1.042  RSLT         =[MEM] mV

  1.043  LIB          selectedCell.FormulaR1C1 = meas;

  1.044  RSLT         =200 kohm
  1.045  DISP         Set the resistance decade for 200 kohm.
#  1.045  SCPI         [@34970]ROUT:CLOS (@313,311)
#  1.045  SCPI         [@34970]ROUT:OPEN (@312,314,315,316,317)
  1.046  LIB          COM selectedCell = worksheet.Range["C54"];
  1.047  LIB          selectedCell.Select();
  1.048  SCPI         [@34970]MEAS:VOLT:DC? (@[V @MuxChan])[I]
  1.049  MATH         meas = MEM * 1000
  1.050  RSLT         =[MEM] mV

  1.051  LIB          selectedCell.FormulaR1C1 = meas;

  1.052  RSLT         =100 kohm
  1.053  DISP         Set the resistance decade for 100 kohm.
#  1.054  SCPI         [@34970]ROUT:CLOS (@314,311)
#  1.054  SCPI         [@34970]ROUT:OPEN (@312,313,315,316,317)
  1.054  LIB          COM selectedCell = worksheet.Range["C55"];
  1.055  LIB          selectedCell.Select();
  1.056  SCPI         [@34970]MEAS:VOLT:DC? (@[V @MuxChan])[I]
  1.057  MATH         meas = MEM * 1000
  1.058  RSLT         =[MEM] mV

  1.059  LIB          selectedCell.FormulaR1C1 = meas;

  1.060  RSLT         =50 kohm
  1.061  DISP         Set the resistance decade for 50 kohm.

  1.062  LIB          COM selectedCell = worksheet.Range["C56"];
  1.063  LIB          selectedCell.Select();
  1.064  SCPI         [@34970]MEAS:VOLT:DC? (@[V @MuxChan])[I]
  1.065  MATH         meas = MEM * 1000
  1.066  RSLT         =[MEM] mV

  1.067  LIB          selectedCell.FormulaR1C1 = meas;

  1.068  HEAD         -2{Input (+) to Output (-)}

  1.069  DISP         Connect the Resistance Decade between
  1.069  DISP         UUT Input (+) and Output (-)

  1.070  RSLT         =500 kohm
  1.071  DISP         Set the resistance decade for 500 kohm.
#  1.073  SCPI         [@34970]ROUT:CLOS (@312,311)
#  1.073  SCPI         [@34970]ROUT:OPEN (@313,314,315,316,317)
  1.072  LIB          COM selectedCell = worksheet.Range["C61"];
  1.073  LIB          selectedCell.Select();
  1.074  SCPI         [@34970]MEAS:VOLT:DC? (@[V @MuxChan])[I]
  1.075  MATH         meas = MEM * 1000
  1.076  RSLT         =[MEM] mV

  1.077  LIB          selectedCell.FormulaR1C1 = meas;

  1.078  RSLT         =200 kohm
  1.079  DISP         Set the resistance decade for 200 kohm.
#  1.082  SCPI         [@34970]ROUT:CLOS (@313,311)
#  1.082  SCPI         [@34970]ROUT:OPEN (@312,314,315,316,317)
  1.080  LIB          COM selectedCell = worksheet.Range["C62"];
  1.081  LIB          selectedCell.Select();
  1.082  SCPI         [@34970]MEAS:VOLT:DC? (@[V @MuxChan])[I]
  1.083  MATH         meas = MEM * 1000
  1.084  RSLT         =[MEM] mV

  1.085  LIB          selectedCell.FormulaR1C1 = meas;

  1.086  RSLT         =100 kohm
  1.087  DISP         Set the resistance decade for 100 kohm.
#  1.091  SCPI         [@34970]ROUT:CLOS (@314,311)
#  1.091  SCPI         [@34970]ROUT:OPEN (@312,313,315,316,317)
  1.088  LIB          COM selectedCell = worksheet.Range["C63"];
  1.089  LIB          selectedCell.Select();
  1.090  SCPI         [@34970]MEAS:VOLT:DC? (@[V @MuxChan])[I]
  1.091  MATH         meas = MEM * 1000
  1.092  RSLT         =[MEM] mV

  1.093  LIB          selectedCell.FormulaR1C1 = meas;

  1.094  RSLT         =50 kohm
  1.095  DISP         Set the resistance decade for 50 kohm.

  1.096  LIB          COM selectedCell = worksheet.Range["C64"];
  1.097  LIB          selectedCell.Select();
  1.098  SCPI         [@34970]MEAS:VOLT:DC? (@[V @MuxChan])[I]
  1.099  MATH         meas = MEM * 1000
  1.100  RSLT         =[MEM] mV

  1.101  LIB          selectedCell.FormulaR1C1 = meas;

  1.102  HEAD         -2{Input (-) to Output (+)}

  1.103  DISP         Connect the Resistance Decade between
  1.103  DISP         UUT Input (-) and Output (+)

  1.104  RSLT         =500 kohm
  1.105  DISP         Set the resistance decade for 500 kohm.
#  1.110  SCPI         [@34970]ROUT:CLOS (@312,311)
#  1.110  SCPI         [@34970]ROUT:OPEN (@313,314,315,316,317)
  1.106  LIB          COM selectedCell = worksheet.Range["C69"];
  1.107  LIB          selectedCell.Select();
  1.108  SCPI         [@34970]MEAS:VOLT:DC? (@[V @MuxChan])[I]
  1.109  MATH         meas = MEM * 1000
  1.110  RSLT         =[MEM] mV

  1.111  LIB          selectedCell.FormulaR1C1 = meas;

  1.112  RSLT         =200 kohm
  1.113  DISP         Set the resistance decade for 200 kohm.
#  1.119  SCPI         [@34970]ROUT:CLOS (@313,311)
#  1.119  SCPI         [@34970]ROUT:OPEN (@312,314,315,316,317)
  1.114  LIB          COM selectedCell = worksheet.Range["C70"];
  1.115  LIB          selectedCell.Select();
  1.116  SCPI         [@34970]MEAS:VOLT:DC? (@[V @MuxChan])[I]
  1.117  MATH         meas = MEM * 1000
  1.118  RSLT         =[MEM] mV

  1.119  LIB          selectedCell.FormulaR1C1 = meas;

  1.120  RSLT         =100 kohm
  1.121  DISP         Set the resistance decade for 100 kohm.
#  1.128  SCPI         [@34970]ROUT:CLOS (@314,311)
#  1.128  SCPI         [@34970]ROUT:OPEN (@312,313,315,316,317)
  1.122  LIB          COM selectedCell = worksheet.Range["C71"];
  1.123  LIB          selectedCell.Select();
  1.124  SCPI         [@34970]MEAS:VOLT:DC? (@[V @MuxChan])[I]
  1.125  MATH         meas = MEM * 1000
  1.126  RSLT         =[MEM] mV

  1.127  LIB          selectedCell.FormulaR1C1 = meas;

  1.128  RSLT         =50 kohm
  1.129  DISP         Set the resistance decade for 50 kohm.

  1.130  LIB          COM selectedCell = worksheet.Range["C72"];
  1.131  LIB          selectedCell.Select();
  1.132  SCPI         [@34970]MEAS:VOLT:DC? (@[V @MuxChan])[I]
  1.133  MATH         meas = MEM * 1000
  1.134  RSLT         =[MEM] mV

  1.135  LIB          selectedCell.FormulaR1C1 = meas;

  1.136  HEAD         -2{Input (-) to Output (-)}

  1.137  DISP         Connect the Resistance Decade between
  1.137  DISP         UUT Input (-) and Output (-)

  1.138  RSLT         =500 kohm
  1.139  DISP         Set the resistance decade for 500 kohm.
#  1.147  SCPI         [@34970]ROUT:CLOS (@312,311)
#  1.147  SCPI         [@34970]ROUT:OPEN (@313,314,315,316,317)
  1.140  LIB          COM selectedCell = worksheet.Range["C77"];
  1.141  LIB          selectedCell.Select();
  1.142  SCPI         [@34970]MEAS:VOLT:DC? (@[V @MuxChan])[I]
  1.143  MATH         meas = MEM * 1000
  1.144  RSLT         =[MEM] mV

  1.145  LIB          selectedCell.FormulaR1C1 = meas;

  1.146  RSLT         =200 kohm
  1.147  DISP         Set the resistance decade for 200 kohm.
#  1.156  SCPI         [@34970]ROUT:CLOS (@313,311)
#  1.156  SCPI         [@34970]ROUT:OPEN (@312,314,315,316,317)
  1.148  LIB          COM selectedCell = worksheet.Range["C78"];
  1.149  LIB          selectedCell.Select();
  1.150  SCPI         [@34970]MEAS:VOLT:DC? (@[V @MuxChan])[I]
  1.151  MATH         meas = MEM * 1000
  1.152  RSLT         =[MEM] mV

  1.153  LIB          selectedCell.FormulaR1C1 = meas;

  1.154  RSLT         =100 kohm
  1.155  DISP         Set the resistance decade for 100 kohm.
#  1.165  SCPI         [@34970]ROUT:CLOS (@314,311)
#  1.165  SCPI         [@34970]ROUT:OPEN (@312,313,315,316,317)
  1.156  LIB          COM selectedCell = worksheet.Range["C79"];
  1.157  LIB          selectedCell.Select();
  1.158  SCPI         [@34970]MEAS:VOLT:DC? (@[V @MuxChan])[I]
  1.159  MATH         meas = MEM * 1000
  1.160  RSLT         =[MEM] mV

  1.161  LIB          selectedCell.FormulaR1C1 = meas;

  1.162  RSLT         =50 kohm
  1.163  DISP         Set the resistance decade for 50 kohm.
#  1.174  SCPI         [@34970]ROUT:CLOS (@314,311)
#  1.174  SCPI         [@34970]ROUT:OPEN (@312,313,315,316,317)
  1.164  LIB          COM selectedCell = worksheet.Range["C80"];
  1.165  LIB          selectedCell.Select();
  1.166  SCPI         [@34970]MEAS:VOLT:DC? (@[V @MuxChan])[I]
  1.167  MATH         meas = MEM * 1000
  1.168  RSLT         =[MEM] mV

  1.169  LIB          selectedCell.FormulaR1C1 = meas;

  1.170  DISP         Disconnect all equipment.

  1.171  LIB          workbook.SaveCopyAs(@savefile);
  1.172  SCPI         [@34970]*RST
#  1.165  CALL         Sub Cleanup Excel
  1.173  END

 

0
Avatar
Tam Tu

I'll have some materials to digest now! Thanks Mike and mghadi...

0
Avatar
Tam Tu

Another question.

Can I open more than 1 EXCEL sheet, say like about 5-10 separate sheets to export immediately the data points, in a batch processing manner for that many separate tool kits? Any sample that I can study?

Thanks for any help.

0
Avatar
Michael Johnston

I don't see why not. It would circumvent the data retention aspect of MET/BASE or MET/TEAM (depending on which you are using), though, since the calibration record generated would be linked to only one of the assets.

To do it, the most certain way would be to re-save the given Excel Workbook after each change and re-open the next one. I don't know if changing the active workbook variable to cause a loss of data or not (i.e., if I have two file name variables like @file in my example above, and I make one active, enter data, open the other, enter data, and then reopen the first). I think it's worth a try, though. Use the same approach from my example, but change your workbook and worksheet objects between steps.

Please sign in to leave a comment.