topic Re: Automating JMP through visual basic: copying excel data to JMP data tables. in Discussions

Automating JMP through visual basic: copying excel data to JMP data tables.

Eduardo — Fri, 05 Jan 2018 18:10:30 GMT

Hello all,

The script below is supposed to do the following: it runs a VBA code which copies the excel workbook data to JMP tables (using the JMP library jmp.tlb). The first part is intended to load the reference JMP in excel so that functions associated with JMP automation can run. The second part exports all the workboook sheets to JMP data tables. When I run this code I get the following error: User-defined type not defined at the

Dim MyJMP As JMP.Application line. If I run the first part alone, the code works: it sets a check mark to the JMP reference (under tools>>references at the VBA editor). If I run the second part (after setting manually the check mark for JMP under tools>>references), it also works. Thus I do not understand what is wrong. Any help appreciated :) many thanks!

Eduardo

Sub export()

'Add JMP reference

'PART 1

On Error Resume Next

Application.VBE.ActiveVBProject.References.AddFromFile _

"C:\Program Files\SAS\JMP\12\jmp.tlb"

On Error GoTo 0

'This part exports all tabs in the excel workbook to JMP as tables

'PART 2

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

End Sub

Re: Automating JMP through visual basic: copying excel data to JMP data tables.

Eduardo — Tue, 23 Jan 2018 13:19:55 GMT

Hi all,

I found myself a solution to my problem but then I got another one. What I'm trying to do is as follows: a JMP script calls a VBS script. The VBS script runs an excel add-in. The excel add-in gets a data file, processes it, and generates a workbook. The VBS then continues and introduces in the workbook two macros. One macro is for activating the JMP reference (jmp.tlb) and the second one is for finally exporting all sheets of the workbook as JMP tables. I have attached the JMP code and the VBS code is below (perhaps this is interesting for other people). Now this is how everything runs: the add-in runs, the workbook is created, but then it hangs (Excel displays later: Microsoft excel is waiting for another application to complete an OLE action). Then I tried to change the syntax of my runprogram command: instead of writing Read function ("text"), I wrote Read function (). The whole thing runs (it even pops the "Finished" message from the VBS) but what comes after the runprogram commad is not working. From the moment I run the JMP script, ntables=0. It looks like the JMP script runs the VBS script and runs in parallel what comes after the runprogram command, and since there are no tables at that point, then ntables=0. I don't understand. I guess I'm using the wrong way the runprogram command and that's my problem. Must be something easy but cannot find it. Any help appreciated! thanks!

File Call myAddin.vbs

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Users\eduret\AppData\Roaming\Microsoft\AddIns\Gamma Tools.xla")

objExcel.Application.Visible = True

objExcel.Application.Run "'Gamma Tools.xla'!Pics"

Set objWorkbook2 = objExcel.ActiveWorkbook

Set xlmodule = objworkbook2.VBProject.VBComponents.Add(1)
strCode = _
"sub ref()" & vbCr & _
"On Error Resume Next" & vbCr & _
"Application.VBE.ActiveVBProject.References.AddFromFile _" & vbCr & _
"""C:\Program Files\SAS\JMP\12\jmp.tlb""" & vbCr & _
"On Error GoTo 0" & vbCr & _
"end sub"
xlmodule.CodeModule.AddFromString strCode

objExcel.Application.Run "ref"

Set xlmodule = objworkbook2.VBProject.VBComponents.Add(1)
strCode = _
"sub export()" & vbCr & _
"Dim MyJMP As JMP.Application" & vbCr & _
"Dim JMPdoc As JMP.Document" & vbCr & _
"Set MyJMP = CreateObject(""JMP.Application"")" & vbCr & _
"MyJMP.Visible = True" & vbCr & _
"Set JMPdoc = MyJMP.OpenDocument(ThisWorkbook.FullName)" & vbCr & _
"Dim JMPdt As JMP.DataTable" & vbCr & _
"Set JMPdt = JMPdoc.GetDataTable" & vbCr & _
"end sub"
xlmodule.CodeModule.AddFromString strCode

objExcel.Application.Run "export"

objworkbook2.Save
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit
WScript.Echo "Finished."
WScript.Quit

Re: Automating JMP through visual basic: copying excel data to JMP data tables.

Eduardo — Wed, 24 Jan 2018 10:37:47 GMT

Hi all,

I managed to narrow down where the problem lies. I simplified everything. Now I have a macro-enabled excel workbook with the following macro:

Sub export()
Dim MyJMP As JMP.Application
Dim JMPdoc As JMP.Document
Set MyJMP = CreateObject("JMP.Application")
MyJMP.Visible = True
Set JMPdoc = MyJMP.OpenDocument(ThisWorkbook.FullName)
End Sub

If I run this macro from excel, it does what it is supposed to do. It exports to JMP the sheets of the workbook as JMP tables.

Then I also have the following VBS script with the following code:

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("\\vfc1home1\home1\eosorio\Profile\Documents\vbs scripts\test.xlsm")

objExcel.Application.Visible = True

objExcel.Application.Run "ref" ---> this loads jmp.tlb

Set objWorkbook2 = objExcel.ActiveWorkbook

objExcel.Application.Run "export" ---> this runs the macro that is giving problems if call from my VBS

objworkbook2.Save
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit
WScript.Echo "Finished."
WScript.Quit

Finally I have the following one line JMP script:

rp = RunProgram( Executable( "WSCRIPT.EXE" ), Options( "\!"\\vfc1home1\home1\eosorio\Profile\Documents\vbs scripts\exportJMP.vbs\!"" ),Read function("text"));

Now this is how everything runs: I run my JMP script, it calls my VBS script, which then finally executes the excel macro export. But when executing the export macro the whole thing hangs and excel produces the following message: Microsoft excel is waiting for another application to complete an OLE action....while the wheel on JMP is spinning :)

Any help super appreciated ! Thanks!

Re: Automating JMP through visual basic: copying excel data to JMP data tables.

Justin_Chilton — Fri, 26 Jan 2018 16:46:12 GMT

Why not move the export Sub to the VBScript file? It sounds like there are issues with running these two tasks simultaniously.

You can automate JMP using the COM interface (without the jmp.tlb type library) from within your VBScript. Check out this white paper for some information about automating JMP in this way, including an example using VBScript.

However, looking at your program flow, it seems like you are just trying to import the Excel worksheet to a JMP table. If so, you can just use the Open() function to do the import.

Re: Automating JMP through visual basic: copying excel data to JMP data tables.

AlexDong — Thu, 24 Jan 2019 08:10:09 GMT

Hi All,

I am looking successfully code for automating JMP through visual basic.

Anyone completed that please share it with me. Appreciate on your help on this topic.

Thank you.

Re: Automating JMP through visual basic: copying excel data to JMP data tables.

Mark_Bailey — Thu, 24 Jan 2019 13:02:29 GMT

Have you read the JMP documentation about automating JMP with Visual Basic? If not, that is the best place to start. (See the Automation Reference PDF in the Documentation folder.) Then see the Samples/Automation folder for example code.

Re: Automating JMP through visual basic: copying excel data to JMP data tables.

jerryspilTC — Wed, 10 Apr 2019 02:51:17 GMT

I wonder why JMP dont develop a platform to create a customizable windows forms or UI within JMP, the reason why we use VB is because we want to fully automate data extractions, data crunching and data analysis together.

Re: Automating JMP through visual basic: copying excel data to JMP data tables.

Mark_Bailey — Wed, 10 Apr 2019 11:34:20 GMT

Select File > New > Application.

Re: Automating JMP through visual basic: copying excel data to JMP data tables.

txnelson — Wed, 10 Apr 2019 13:44:35 GMT

I will add to @Mark_Bailey by pointing you to the documentation on the Application Builder

Help==>Books==>Scripting Guide

Look for the chapter on "Creating Applications"

Besides using the Application Builder, the JMP Scripting Language has a full set of Display Objects that allow the building of whatever UI you want. Examples are found in the Sample Scripts and Apps supplied with JMP, and also in the many JMP Addins available in the JMP File Exchange on the JMP Website