nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How to run jsl script in excel VBA?

rainwang

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!

Accepted answers

briancorcoran

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

All comments

briancorcoran

Hi,

I hope that helps.

Brian Corcoran

JMP Development

rainwang

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.

briancorcoran

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

rainwang

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.