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
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
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!
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.