05-10-2014 09:38 AM - edited 05-10-2014 09:41 AM
Does anyone have some example code to create a VBS Excel OLE interface that passes two data channels to an Excel spreadsheet then calls one of the many Excel statistical functions (Chi, T, etc.) which then returns the result? I don't have much experience with OLE automation but I need automated access to Excel statistical analysis functions (or go through the nightmare of writing my own or linking to public domain .DLL functions).
Thank you!
Eric
05-11-2014 06:03 PM
Eric
Here is some code that will access Excel from DIAdem.
Version:0.9 StartHTML:00000107 EndHTML:00003624 EndFragment:00003584 EndFragment:00000000 ' Read an Excel Spreadsheet
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open _
("C:\Scripts\New_users.xls")
intRow = 2
Do Until objExcel.Cells(intRow,1).Value = ""
msgbox( "CN: " & objExcel.Cells(intRow, 1).Value
intRow = intRow + 1
Loop
objExcel.Quit
Should be able to access the excel functions from this interface. Would suggest that you develop your code in Excel VBA and then port the code over to DIAdem VBS. (much better troubleshooting and intellisense in Excel vba)
DIAdem has some real good statistic function. Look up the command StatBlockCalc
Paul
05-15-2014 08:04 PM - edited 05-15-2014 08:06 PM
Hope this helps someone else with a similar problem 🙂
The following function solves my problem and can be easily altered to use other Excel functions.
Function Excel_T_Test(oCh1, oCh2)
' Creates an Excel object and receives data from two channels which can be different lengths
' then calls the student T test function
' The function then returns the result
' If there was an error the function returns -1
On Error Resume Next
Dim oXL ' Excel application
Dim oBook ' Excel workbook
Dim oSheet ' Excel Worksheet
Dim iRowCh1, iRowCh2 ' Channel Lengths
Dim iRow, iCol ' Index variables
Dim i
'Start Excel and create a new workbook
Set oXL = CreateObject("Excel.application")
oXL.DisplayAlerts = False
Set oBook = oXL.Workbooks.Add
Set oSheet = oBook.Worksheets.Item(1)
oXL.Visible = true
iCol = 3
iRowCh1 = oCh1.Size
iRowCh2 = oCh2.Size
if iRowCh1 > iRowCh2 then
iRow = iRowCh1
else
iRow = iRowCh2
end if
For i = 1 To iRowCh1
oSheet.Cells(i, 1) = oCh1.Values(i)
Next
For i = 1 To iRowCh2
oSheet.Cells(i, 2) = oCh2.Values(i)
Next
'Performs a two tail student T test with Two-sample unequal variance (heteroscedastic)
oSheet.Cells(1, 3).Formula = "=TTEST(A1:A" & iRowCh1 & ",B1:B" & iRowCh2 & ",2,3)"
Excel_T_Test = oSheet.Cells(1, 3).Value
'clean up
oBook.Close
oXL.Quit
Set oXL = Nothing
Set oBook = Nothing
Set oSheet = Nothing
If Err.Number <> 0 Then
Excel_T_Test = -1
End If
End Function
10-29-2014 08:49 AM
Bonjour ,
je cherche des script pour faire des interfaces sur DIAdem si vous pouvez m'aider svp
codialement
10-30-2014 09:43 AM
Hello,
We have a French language forum here: http://forums.ni.com/t5/Discussions-au-sujet-des-autres/bd-p/4170
They should be able to better assist you there.
Also, if you're looking for VB script code to interface DIAdem to Excel, did the code above work at all for you? What have you tried so far and how did it work out?