VBA - Save Excel workbook as JMP data table

[#1]

Hello, I'm familiar with Excel VBA but new to JMP scripting. I'm currently using JMP 11.

I'd like to open an Excel workbook (with a single worksheet) and save it as a JMP data table, so I can run a JMP analysis on it.

How do I reference the unsaved JMP data table created by this VBA code?

Dim app As JMP.Application
Dim doc As JMP.Document
Dim dt  As JMP.DataTable

'Start JMP
Set app = CreateObject("JMP.Application")
app.Visible = True
 
'Open Excel workbook containing single sheet of data
app.OpenDocument ("C:\jmp\Data.xlsx")

When I prefix the last line with "dt = ", I get an error.

Any suggestions would be appreciated.

[#2]

OK, I got it to work:

Dim app As JMP.Application

Dim doc As JMP.Document

'Start JMP

Set app = CreateObject("JMP.Application")

app.Visible = True

'Open Excel workbook and save as JMP data table

Set doc = app.OpenDocument("C:\jmp\Data.xlsx")

doc.SaveAs ("C:\jmp\Data.jmp")

[#3 · Accepted solution]

You should be able to do:

doc = app.OpenDocument ("C:\jmp\Data.xlsx")

dt = doc.GetDataTable();

The sample programs included with your JMP install can help you, the Data Table sample in particular.

Brian Corcoran

JMP Development

[#4]

Thanks, Brian. This is was just what I needed.

[#5]

Super easy to do in JSL.  Here is some example code for one way to approach this.  Copy the code into a new JSL window (under File > New...) and click the run script at the top of the window.

Names Default to Here(1);

dt = Open();

dt << Save As("Excel Data.xlsx");

Hope that helps you out!

M