topic JMP - Excel VBA Automation in Discussions

JMP - Excel VBA Automation

Bala — Thu, 12 Jan 2017 16:43:11 GMT

Hi, I am using JMP automation to get particular row number based on the selected data using "SelectRowsWhere" function, but I could not get the row number value is there any other way to get the row number. see below my code.

Sub getrownum()
Dim MyJMP As JMP.Application
Dim JMPDoc As JMP.Document
Dim DT As JMP.DataTable
Set MyJMP = CreateObject("JMP.Application")
Set JMPDoc = MyJMP.OpenDocument("D:\JMP Main Data Table\TestData.jmp")
MyJMP.Visible = True
Set DT = JMPDoc.GetDataTable
Set col1 = JMPDoc.GetDataTable.GetColumn("Date")

'DT.SelectRows 102056, 102057
'to get the row number 102065 ??
DT.SelectRowsWhere "Date", rowSelectEquals, rowSelectClearPrevious, "3565986300"
noofrowselected = DT.GetNumberOfRowsByRowState(rowStateSelected)
nn = DT.EnumRowStatesBegin(rowStateSelected)
n1selectedrow = DT.EnumRowStatesGetNextRow()
n2selectedrow = DT.EnumRowStatesGetRowByIndex(1)
getronumff = n1selectedrow

End Sub

- Bala

Re: JMP - Excel VBA Automation

briancorcoran — Thu, 12 Jan 2017 20:48:09 GMT

Hi Bala,

I would look at the "Data Table .NET" sample program that ships with JMP. For JMP 13, this would be under C:\program files\sas\jmp\13\Samples\Automation\Visual Basic Samples. There are a couple ways to do this. One, which you were getting at, is the Enum. The sample contains a button called Enum Selected that does what you want. The relevant code is:

NSelected = DT.EnumRowStatesBegin(JMP.rowStateConstants.rowStateSelected)
For i = 1 To NSelected
MsgBox(DT.EnumRowStatesGetNextRow, MsgBoxStyle.OkOnly, "Next Selected Row Is")
Next i

You can also look at the "Get Selected Vector" example. This uses the call GetRowStateVector to return an array of all the selected rows by index.

The "Get Selected Data", another example, uses GetRowStateVectorData to return the underlying data values rather than the indices.

I hope that helps.

Brian Corcoran

JMP Development

Re: JMP - Excel VBA Automation

briancorcoran — Thu, 12 Jan 2017 20:52:16 GMT

I should also mention that if you want individual column values, then you should obtain a column object from the data table object, and then just call GetCellVal on that.

For instance:

Col = DT.GetColumn("age")

val = Col.GetCellVal(index)

Re: JMP - Excel VBA Automation

Bala — Fri, 13 Jan 2017 01:26:17 GMT

Hi Brian Corcoran,

Thanks for the reply, yes I am using the "Data Table .NET" as sample. Previously the problem is I could not get the Row index (row number) value using the "SelectRowsWhere", that could be due to the value type. Now I changed the code to following and it solves the purpose.

' **DT.SelectRowsWhere "Date", rowSelectEquals, rowSelectClearPrevious, "3565986300"**
DT.SelectRowsWhere "Date", rowSelectEquals, rowSelectClearPrevious, "12-31-2016 12:00:00 AM"

noofrowselected = DT.GetNumberOfRowsByRowState(rowStateSelected)
nn = DT.EnumRowStatesBegin(rowStateSelected)
n1selectedrow = DT.EnumRowStatesGetNextRow()
n2selectedrow = DT.EnumRowStatesGetRowByIndex(1)
getronumff = n1selectedrow