Subscribe Bookmark RSS Feed

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

Highlighted
Eduardo

Occasional Contributor

Joined:

Aug 5, 2017

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
3 REPLIES
Eduardo

Occasional Contributor

Joined:

Aug 5, 2017

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

Eduardo

Occasional Contributor

Joined:

Aug 5, 2017

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!

Justin_Chilton

Joined:

Aug 27, 2015

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.

Justin