Subscribe Bookmark RSS Feed

How can I get only one sheet of excel file as JMP data table?

hmirzasg0

New Contributor

Joined:

Aug 18, 2016

Hi,

I am using below VBA to get JMP data table from excel file. The problem is that JMP takes all sheets of the excel file as data table but i need JMP to get only one specific sheet (i.e. sheet1) as data table. I appreciate your help to solve it.

Set MyJMP = CreateObject("JMP.Application")

Set JMPDoc = MyJMP.OpenDocument(InstallDir + "D:\Final Platform..xlsm")

Set DT = JMPDoc.GetDataTable

1 ACCEPTED SOLUTION

Accepted Solutions
Solution

Unfortunately, it's kind of an all or nothing with RunCommand.  Once you use JSL, you must use all JSL.  There is also the RunJSLFile if you can encapsulate all of your operations in JSL.  It's not possible to mix and match automation objects and JSL references, outside of looking at display tree elements.

Brian

6 REPLIES
briancorcoran

Joined:

Jun 23, 2011

The full Excel Wizard JSL interface hasn't been built into Automation, but is still available to you via the RunCommand method in Automation.  You could do something like this (I successfully used this just now):


MyJMP.RunCommand("Open("C:\Excel Wizard Demo\TexasPrecipitationMultiple.xlsx",Worksheets( "2008" ))

In general, you can use the Excel Wizard within JMP to open any Excel workbook to your specifications, and then you can take the Source script in the resulting JMP data table and use it in RunCommand.  I hope this helps.

Brian Corcoran

JMP Development

hmirzasg0

New Contributor

Joined:

Aug 18, 2016

Hi Brian,

Many thanks for your reply. There is an error when I try your code (pasted below). I appreciate your help:

12554_JMP.png

briancorcoran

Joined:

Jun 23, 2011

Sorry, I didn't properly escape the quoting during the paste into the forum post. 

myJMP.RunCommand("Open(""C:\Excel Wizard Demo\TexasPrecipitationMultiple.xlsx"",Worksheets( ""2008"" ));")

Brian

hmirzasg0

New Contributor

Joined:

Aug 18, 2016

Thanks Brian. It works now and opens one sheet as JMP document. The new issue is how to assign a variable to this opened document and run FitModel command. Before using RunCommand, I assigned as follow:

Set JMPDoc = MyJMP.OpenDocument(InstallDir + "D:\Final Platform - 190816.xlsm")

I can not use the same method as I am using RunCommand. It means that I can not assign as follow:

Set JMPDoc = MyJMP.RunCommand("Open(""D:\Final Platform - 190816.xlsm"",Worksheets( ""Model"" ));")

I am also not able to use RunCommand to run FitModel because the dependent and independent variables are changing based on user choices:

MyJMP.RunCommand ("Open(""D:\Final Platform - 190816.xlsm"",Worksheets( ""Model"" ));")

Set JMPDoc= ?????-----How to assign????

Set FM = JMPDoc.CreateFitModel

FM.LaunchAddY (Sheet6.Cells(1, 1))

Solution

Unfortunately, it's kind of an all or nothing with RunCommand.  Once you use JSL, you must use all JSL.  There is also the RunJSLFile if you can encapsulate all of your operations in JSL.  It's not possible to mix and match automation objects and JSL references, outside of looking at display tree elements.

Brian

hmirzasg0

New Contributor

Joined:

Aug 18, 2016

Ok noted with thanks Brian. Your responses were quick and really useful. I should be appreciative for your time. I am developing a forecasting interface by connecting JMP and Excel and may bother you in future for more helps

All the best,

Hassan