topic Re: Modifying an excel file from jmp in Discussions

Modifying an excel file from jmp

Luis180189 — Wed, 06 Jun 2018 16:10:07 GMT

Hello,

Is there a way to modify an existing EXCEL file (a single particular row) and save it back on the original folder from jmp?

Any help would be appreciated.

Thank you!

Re: Modifying an excel file from jmp

cwillden — Wed, 06 Jun 2018 16:24:32 GMT

You could import the excel file, modify, and then export back to an xlsx format, but you would lose any Excel-specific functionality in doing so. This would only work if you had a spreadsheet of just column headers and data with no formulas, special formatting, etc.

Re: Modifying an excel file from jmp

Luis180189 — Wed, 06 Jun 2018 16:33:38 GMT

Im looking for a JSL script that allows me to do this.

Re: Modifying an excel file from jmp

cwillden — Wed, 06 Jun 2018 18:50:49 GMT

In the spirit of teaching to fish rather than handing out fish, I'm going to point you in the right direction. First step is to let JMP write the script for you to import the table. Import one of your excel files manually, using Excel Import Wizard if necessary. The resulting table will have a script titled "Source" that contains the script to import the Excel file. It might look something like this:

Open(
	"filename.xlsx",
	Worksheets( "Sheet 1" ),
	Use for all sheets( 1 ),
	Concatenate Worksheets( 0 ),
	Create Concatenation Column( 0 ),
	Worksheet Settings(
		1,
		Has Column Headers( 1 ),
		Number of Rows in Headers( 1 ),
		Headers Start on Row( 1 ),
		Data Starts on Row( 2 ),
		Data Starts on Column( 1 ),
		Data Ends on Row( 0 ),
		Data Ends on Column( 0 ),
		Replicated Spanned Rows( 1 ),
		Replicated Spanned Headers( 0 ),
		Suppress Hidden Rows( 1 ),
		Suppress Hidden Columns( 1 ),
		Suppress Empty Columns( 1 ),
		Treat as Hierarchy( 0 ),
		Multiple Series Stack( 0 ),
		Import Cell Colors( 0 ),
		Limit Column Detect( 0 ),
		Column Separator String( "-" )
	)
);

This is the start of your own script. Copy it over to a script window. To put a handle on the data table from the imported Excel file, just put a "<var name> = " before "Open(".

Next, you need to do whatever modification to the table contents. You can access a specific value in the desired row and column and set the value using something like this:

dt:<colname>[<row number>] = <desired value>

If the column name has any special characters, then use dt:Name("<column name>")[<row number>].

Last, you need to export the results. There are a few other posts on the community that walk you through saving a JMP table in the .xlsx format. Here's a recent one: https://community.jmp.com/t5/Discussions/Saving-Data-Table-to-Excel-format/td-p/40392

I strongly encourage you to try and put the pieces together on your own. That's the only way you will become proficient at scripting. If you do get stuck on any particular part, we're here to help.

Re: Modifying an excel file from jmp

gzmorgan0 — Thu, 07 Jun 2018 10:58:13 GMT

If you are using the Window OS and know a little VBscript, you can do this using Run Program.

The attached script uses wscript (as far as I know always on Windows OS). It opens Big Class then saves it as Excel to emulate, an existing Excel workbook.

The simple VBscript changes the value of Row E3 and colors the cell yellow. This script is using JSL to write the VBscript, however, the script could exist, and just be executed from JMP or numerous other methods.

JMP uses wscript to execute the VBscript that was saved. This is one option to update a Workbook and not lose the other features of Excel.