I have a data table in excel that I am exporting to JMP using VBA. I am able to apply colors to the JMP datatable rows through the SetSelectedRowsColor function, but am only able to apply default JMP colors (jmpColorBlue, jmpColorGreen, etc). An example of what I am doing is:
Dim MyJMP As JMP.Application
Dim JMPdoc As JMP.Document
Set MyJMP = CreateObject("JMP.Application")
MyJMP.Visible = True
Set JMPdoc = MyJMP.OpenDocument(ThisWorkbook.FullName)
Dim JMPdt As JMP.DataTable
Set JMPdt = JMPdoc.GetDataTable
rws_bool = JMPdt.SelectRowsWhere("Condition", rowSelectEquals, rowSelectClearPrevious, "1")
JMPdt.SetSelectedRowsColor (jmpColorBlue)
I would instead like to use the colors in the Excel table and apply those to the JMP rows. Is there a way to apply the interior Excel cell RGB to the rows in JMP?
Thanks
I'm sorry, but there is no mechanism right now to put in straight RGB values. It seems like it might be better if the Excel import mechanism just handled the cell coloration for you, rather than making you do it through VB. Is this true?
Brian Corcoran
JMP Development
thanks for your quick response Brian!
The above code goes on to perform a package of analyses that we want out of our data (variability charts, oneway analyses, heat maps, etc.) which is why I would like to use VB. I was hoping that the data in those charts would be able to have the same coloring that the user has set up in Excel.
When you say Excel import mechanism, are you speaking to the Excel add in?
No, if you specify an Open command along with a file that has an XLSX extension, JMP will open that file directly from disk with no COM interfaces involved. That import mechanism might be able to be extended to read the cell colors and change the cells within the resulting JMP table.
Brian
It works great!
Here is the final code, where the condition number which I am coloring by is in column A
Dim MyJMP As JMP.Application
Set MyJMP = CreateObject("JMP.Application")
MyJMP.Visible = True
Dim str As String
Dim str2 As String
str = "dt = Open( " & """" & ThisWorkbook.FullName & """" & " );"
Dim rgbvalue, rvalue, gvalue, bvalue
Dim cl As Range
Set cl = ThisWorkbook.ActiveSheet.Range("A2")
Dim rgbstr As String
Dim cond As Integer
Do Until cl.Value = ""
Do Until cl.Offset(1).Value <> cl.Offset.Value
Set cl = cl.Offset(1)
Loop
cond = cl.Value
'Find RGB value of Excel cell
rgbvalue = cl.Interior.Color
rvalue = (rgbvalue Mod 256) / 255
gvalue = (rgbvalue / 256 Mod 256) / 255
bvalue = (rgbvalue / 65536 Mod 256) / 255
rgbstr = "{" & rvalue & "," & gvalue & "," & bvalue & "}"
str2 = "dt << Select Where(Condition Number==" & cond & ") << colors(" & rgbstr & ");"
str = str & str2
Set cl = cl.Offset(1)
Loop
MyJMP.RunCommand (str)
Thanks for your help, Brian!