Re : VBS OLE Excel interface for statistical analysis

[Radi8]

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

[Pesmith8]

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

[Radi8]

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

[ndiayes12]

Bonjour ,

je cherche des script pour faire des interfaces sur DIAdem si vous pouvez m'aider svp

codialement

[James-B]

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?