How to run jsl script in excel VBA?

[#1]

Try to run jsl script in excel VBA envirment. the purpose of the script is to open two csv data tables and join them together.

the script likes below:

Sub loadData()
Dim myjmp As JMP.Application
Dim jmp_doc As JMP.Document
Dim jmp_BL  As JMP.DataTable
Dim jmp_dt  As JMP.DataTable
Dim jmp_dtFinal  As JMP.DataTable
Dim jmpstr     As String

Set myjmp = CreateObject("JMP.Application")
myjmp.Visible = True
Set jmp_doc = myjmp.OpenDocument("C:\Users\xxxxx\Desktop\BL.csv")
Set jmp_BL = jmp_doc.GetDataTable

Set jmp_doc = myjmp.OpenDocument("C:\Users\xxxxx\Desktop\cbData.csv")
Set jmp_dt = jmp_doc.GetDataTable

jmpstr = "jmp_dtFinal= jmp_dt << Join(With(jmp_BL),Merge Same Name Columns,By Matching Columns( :col1 = :col2, :col2= :col2),Drop multiples( 0, 1 ),Name( ""Include non-matches"" )(1, 1),);"
myjmp.RunCommand jmpstr

The two data tables can be opened correctly, but only the join function, it dosen't work. Can anybody help on the script?

Thank you!

[#2 · Accepted solution]

Hi,

You can't mix object references between JSL and Automation. If you need to reference an object like a data table, you must do it all in Automation or all in JSL.  There is an automation Join method available to Datatable objects.  There is also an "AddToJoinList" method to add the columns that you wish to join on.  The Automation reference guide can help with this, as can the Datatable automation sample program that ships with JMP.  For JMP 12, the sample program is available in C:\Program Files\SAS\JMP\12\Samples\Automation\Visual Basic Samples\Data Table .NET. The Automation reference guide, "Automation Reference.pdf" is available in C:\Program Files\SAS\JMP\12\Documentation.

I  hope that helps.

Brian Corcoran

JMP Development

[#3]

Hi, Brain

Thank you for your information.

This is helpful for me. while I suffer another issue when refer to the Data Table .NET. There are some examples in Data Table .NET. And I follow the example to finish my script.

Below is the example in DATA Table. NET

While I try to use the similar way, but seems when I set the JoinMatchOptions, there's always a error. seems i need set a variable for this script.

while I can't find any help information for this JoinMatchOptions. Not sure if I miss any setting to enable the JMP automation, I already include all the JMP related items in the reference.

[#4]

I would pass False and True rather than 0 and 1,like the example does.  However, it is more likely that there is an issue with the import of your data for DT4.  It's difficult for me to tell if that table has been imported successfully.

Brian Corcoran

JMP Development

[#5]

Hi, Brian

Maybe I didn't discript it clearly.

When I try to use the example in the  Data Table .NET to join tables, I found the example can't work correctly.

For example:

When I open a data table, I can't only use below script:

        Dim Doc4 As JMP.Document

        Dim DT4 As JMP.DataTable

        Doc4 = MyJMP.OpenDocument(tempDir & "cbData.csv")

        DT4 = Doc4.GetDataTable

Instead, I need add Set at begging of the scripts as below:

        Set Doc4 = MyJMP.OpenDocument(tempDir & "cbData")

        Set DT4 = Doc4.GetDataTable

After that, both data table can import correctly.

while when go to set the join matching options, when I use below script:

        DT4.SetJoinMatchOptions(True, False)

There is always an error, seems I also need send this to a variable. But  I am not quite sure what's the type of the return value of "DT4.SetJoinMatchOptions(True, False)". Maybe change the script as below:

       Dim str as  xxxxxx

       set str=DT4.SetJoinMatchOptions(True, False)

I am not sure if my assumption is right or not, just don't know how this JoinMatchOptions can set.

[#6]

Hi,

The use of "Set" for objects can be optional in Visual Studio when using VB, so the samples don't always use it.  They were also written for use by early versions of Visual Studio like 2010.  Apparently VBA in Excel has its own unique quirks with syntax.  In your case, it does not want parenthesis around the options for SetJoinMatchOptions.  An alternative is to use Call in front of it.  Here is a working sample from my Excel 2013

        'The second example is a join using matching columns from two like

        'datatables.

        Dim Doc4 As JMP.Document

        Dim Doc5 As JMP.Document

        Dim NewDT2 As JMP.DataTable

        Dim DT4 As JMP.DataTable

        Dim DT5 As JMP.DataTable

        Set MyJMP = CreateObject("JMP.Application")

        MyJMP.Visible = True

        Set Doc4 = MyJMP.OpenDocument("C:\Program Files\SAS\JMP\13\Samples\Data\Ingots.jmp")

        Set Doc5 = MyJMP.OpenDocument("C:\Program Files\SAS\JMP\13\Samples\Data\Ingots2.jmp")

        Set DT4 = Doc4.GetDataTable

        Set DT5 = Doc5.GetDataTable

        DT4.AddToJoinMatchList (("Heat"))

        DT4.AddToJoinMatchList (("Soak"))

        ' Set options to Drop Multiples and Not include non-matches

        DT4.SetJoinMatchOptions True, False

        DT5.AddToJoinMatchList (("Heat"))

        DT5.AddToJoinMatchList (("Soak"))

        DT5.SetJoinMatchOptions True, False

        Set NewDT2 = DT4.Join(DT5, JMP.dtJoinConstants.dtJoinMatching, "Join Table")

Brian

[#7]

Hi, Brian

Thank you so much for your information.

I have one more question about this data join. When I set the  Join Match Options as below to drop multiples and cinlude non-matches in both main and with tables.

  DT4.SetJoinMatchOptions False, True

  DT5.SetJoinMatchOptions True, True

But the result come out as below, seems these two table joined, but didn't merge with the same name column.

    

Normally in jmp data table join setting, there is one option "Merge same name column", if check that one, the table will merge columns with same name. But in this VBA join fuction:

     Set NewDT2 = DT4.Join(DT5, JMP.dtJoinConstants.dtJoinMatching, "Join Table")

There is no such setting.

So do you know how to achive the function of "nerge same name column"?

thank you!

[#8]

I'm sorry, but that option hasn't made it into Automation yet.  You can delete the columns in the result table using automation.  Also, if you don't mind doing the table merge into one of the existing tables, you might be able to use UpdateTable instead.

Brian

[#9]

, Hoping it will be added in next jmp version.

Thank you!

[#10]

Yes, it will be added in JMP 13.  Thanks,

Brian