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

[#1]

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

[#2]

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

[#3]

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!

[#4 · Accepted solution]

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.