JMP - Excel VBA Automation

[#1]

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

[#2 · Accepted solution]

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

[#3]

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)

[#4 · Accepted solution]

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