Export Excel Colors to JMP Data Table with Visual Basic

[#1]

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

[#2]

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

[#3]

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?

[#4]

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

[#5]

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!