topic Re: Scripting: Open table and closing it in Discussions

Scripting: Open table and closing it

JL_LZH — Thu, 24 Oct 2019 09:10:21 GMT

Hello,

I'm new to scripting in JSL and it seems like I'm struggling right from the start..

I want to import an Excel table and then do several changes and so on. But it seems like the easiest operations doesn't work.

My idea was to start really simple: opening and closing it. I imported my Excel table with the wizard and saved the script. I altered it to the following (added a variable dt and tried closing the table:

dt = Open(
	"I:\myfile.xlsx",
	Worksheets( "mydata" ),
	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( "-" )
	);
Close (dt);
);

It opens the table but it's not closing. What am I doing wrong?

Thanks in advance

Re: Scripting: Open table and closing it

ron_horne — Thu, 24 Oct 2019 09:24:12 GMT

Hi @JL_LZH

i think you have a small parentheses issue, try the following:

dt = Open(
	"I:\myfile.xlsx",
	Worksheets( "mydata" ),
	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( "-" )
	)
);
Wait( 0.1 );
Close( dt );

Re: Scripting: Open table and closing it

JL_LZH — Thu, 24 Oct 2019 09:27:21 GMT

Perfect, thanks for the quick reply!
I thought I need the parantathese to end the script.... lesson learned.