topic Re: Script to ask for User input when using "Create Excel Workbook" in Discussions

Script to ask for User input when using "Create Excel Workbook"

EH1 — Fri, 09 Jun 2023 19:52:18 GMT

Hi,

When using "Create Excel Workbook" to export a table to Excel, is there a way to ask the User (possibly via a pop-up window) to input the name that should be used for the Excel tab?

Create Excel Workbook ("c:/results.xlsx",{dtFINAL},{"ASK FOR USER INPUT"});

Thank you!

Re: Script to ask for User input when using "Create Excel Workbook"

txnelson — Mon, 12 Jul 2021 23:12:30 GMT

Here is a very simple example. It contains minimal formatting, no exception coding etc. You need to read the Scripting Guide. You may find it more convenient to develop your code using Dashboard Builder or Application Builder. But most importantly, you need to spend the time reading about JSL, so you can learn to solve the simple problems like this one

Names Default To Here( 1 );
nw = New Window( "Enter",
	<<modal,
	H List Box(
		Text Box( "Enter name for Excel Tab" ),
		Spacer Box( size( 10, 0 ) ),
		theInput =
		Text Edit Box( "",
			<<setwidth( 100 ),
			<<set script(
				theName = theInput << get text
			)
		)
	)
);

Create Excel Workbook( "c:/results.xlsx", theName );

Re: Script to ask for User input when using "Create Excel Workbook"

EH1 — Tue, 13 Jul 2021 00:04:09 GMT

Hi Txnelson - thank you for your help!

The text box pop-up works for me, but it will not save the user inputed name to an Excel tab (and the Excel tab is not even created). I believe I need to incorporate "dtFINAL" in the "Create Excel Worksheet" script somehow as that is the table I want to save in an Excel tab. This is my full script:

Set Default Directory ( "c:/");
 
dt = Open ("616641_US JTBD Final.jmp");
 
md = dt << MaxDiff(
       One Table( 1 ),
       Subject ID( :Subject ID ),
       Choice Set ID( :Choice Set ID ),
       Profile ID( :Response Indicator ),
       Profile Effects( :"Profile ID (Milkshake)"n ),
       Response Value Indicates Best( 1 ),
       Response Value Indicates Worst( -1 )
);
 
md << Save Utility Formula;
md << close window;
 
tb = dt << Tabulate(
       Add Table(
              Column Table( Analysis Columns( :Utility Formula ), Statistics( Mean, N ) ),
              Row Table( Grouping Columns( :"Profile ID (Milkshake)"n ) )
       )
);
 
tb << Make Into Data Table;
dtFINAL = Current Data Table ();
tb << close window;

//Script from TxNelson
Names Default To Here( 1 );
nw = New Window( "Enter",
	<<modal,
	H List Box(
		Text Box( "Enter name for Excel Tab" ),
		Spacer Box( size( 10, 0 ) ),
		theInput =
		Text Edit Box( "",
			<<setwidth( 100 ),
			<<set script(
				theName = theInput << get text
			)
		)
	)
);

// This portion doesn't work
Create Excel Workbook( "c:/results.xlsx", theName );

Close (dtFINAL, nosave);

Data Table( "616641_US JTBD Final" ) << Delete Columns( :Utility Formula );

Re: Script to ask for User input when using "Create Excel Workbook"

txnelson — Tue, 13 Jul 2021 01:25:32 GMT

My error.

As shown in the Scripting Index for the Create Excel Workbook, both the data table(s) to be used and the name of the tab(s) in the Excel workbook need to be specified, and they both need to be entered as JMP lists.

Names Default To Here( 1 );
dt1 = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt2 = Open( "$SAMPLE_DATA/Abrasion.jmp" );
Create Excel Workbook(
	"$TEMP/MyWorkbook.xlsx",
	{dt1, dt2},
	{"Big", "Abrasive"}
);

You can specify the dtFinal data table reference by hard coding it as

{dtFinal}

but you will need to create a new list to hold the name entered by the user. You will want to use something like

theList = {};
insert into(theList, theName);

and then specify the chard code dtFinal and theList values into the Save Excel Workbook() function call.

Re: Script to ask for User input when using "Create Excel Workbook"

EH1 — Tue, 13 Jul 2021 11:25:13 GMT

Thank you, @txnelson!! That worked perfectly!! :)