cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
Bala
Level I

JMP - Excel VBA Automation

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 SOLUTIONS

Accepted Solutions

Re: JMP - Excel VBA Automation

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

View solution in original post

Bala
Level I

Re: JMP - Excel VBA Automation

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

View solution in original post

3 REPLIES 3

Re: JMP - Excel VBA Automation

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

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)

Bala
Level I

Re: JMP - Excel VBA Automation

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