Export Excel Colors to JMP Data Table with Visual Basic

Question

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

cassm · Answer

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!

briancorcoran · Answer

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

cassm · Answer

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?