Automating JMP through Visual Basic (VBA code to import excel data table to JMP)

Question

Hi All,

I just want to load data from "combine" sheet to JMP by using VBA code.

From JMP menu bar below, actually I can directly click to each option and it also works.

But I prefer to do it by VBA coding instead of click directly, may I know how can I touch it by VBA code?

Thanks for help.

gzmorgan0 · Answer

As @briancorcoran stated your task needs clarification.

* Is this a scheduled task using a VBA script that the creates the Excel sheet and once it has been saved you want to kick-off a JMP/JSL script to do an analysis or to modify the Excel sheet in place? 
* Or has the Excel sheet been created and you open JMP and run a JSL script to do an analysis?
* Or other?

A couple weeks ago, a user wanted to change a sheet in place. You might be interested in this post.

https://community.jmp.com/t5/Discussions/Can-JMP-Support-Excel-Formatting-through-JSL/m-p/94892#M38766

Below  is the vbs code to run a JSL script called test_vbs.jsl. You likely want to specify  MyJMP.visible to be False.

On Error Resume Next
On Error Goto 0

Dim MyJMP 
Set MyJMP = CreateObject("JMP.Application")
MyJMP.Visible = True

rem MyJMP.OpenDocument("c:	emp	est_vbs.jsl")
rem MyJMP.Quit

AlexDong · Answer

Hi Brian,

Thanks a lot. I prefer to option#2 but I do not know how to trigger button by VBA code.

The excel information will be saved first before load to JMP data table.

Thank you,

Alex

briancorcoran · Answer

I don't have a specific code sample.  However, there are two approaches that I can think of. One is that you simply save the contents of the table using VBA code to disk, and then start up JMP and issue an Open command to return a Document object.

The second option is to open JMP, and individually retrieve the cell values from Excel and put them into a JMP data table that you have created using NewDataTable.

In both cases, you still need a trigger.  The button serves as this with the UI.  When do you want to save out the Excel information? One option is to do it when the worksheet itself loads.  The other is when a cell changes.  In both cases, the Excel VBA supports triggers that tell you when these events happen.

Brian Corcoran

JMP Development