How to run jsl script in excel VBA?<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />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!
Re: How to run jsl script in excel VBA?Yes, it will be added in JMP 13. Thanks,
Brian
Re: How to run jsl script in excel VBA?, Hoping it will be added in next jmp version.
Thank you!
Re: How to run jsl script in excel VBA?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
Re: How to run jsl script in excel VBA?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!
Re: How to run jsl script in excel VBA?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
Re: How to run jsl script in excel VBA?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.
Re: How to run jsl script in excel VBA?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
Re: How to run jsl script in excel VBA?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.
Re: How to run jsl script in excel VBA?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
