topic Re: Beginner : Help with opening single worksheet and worksheet setting in Discussions

Beginner : Help with opening single worksheet and worksheet setting

hs2021 — Sat, 10 Jun 2023 23:39:04 GMT

I have this script which allows me to select the worksheet as well as set the worksheet settings when I put in the file path :

Open(
	"$DESKTOP/JMP practice/Practive_1.xlsx",
	Worksheets( "Data_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( 49 ),
		Data Starts on Row( 50 ),
		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( "-" )
	)
);

But when I just try to write a script to let the user select a file to open with the command :

open ()

It just opens all the work book and not letting me edit the workbook settings, I need a command which lets me edit the worksheet settings even when the file path isn't mentioned . Please Help

Re: Beginner : Help with opening single worksheet and worksheet setting

txnelson — Sat, 23 Oct 2021 01:13:55 GMT

Use a Pick File() function to select the file to open, and then take the results of the Pick File and reference it in the Open() function.

theFile = Pick File(....);
Open( theFile,
other loading options);

Re: Beginner : Help with opening single worksheet and worksheet setting

hs2021 — Mon, 25 Oct 2021 16:15:03 GMT

Hi txnelson,

the pick file works well , but i am still having issues :

theFile = Pick File();

Open(
	"theFile",
	Worksheets( "Results"),
	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( 49 ),
		Data Starts on Row( 50 ),
		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( "-" )
	)
);

Even when I put this it show's an error " There was an error opening the file in the excel wizard... using all the open all sheets preference should work in this case" I trying to work it out, hopefully will find a way to get it done, Thanks !

Re: Beginner : Help with opening single worksheet and worksheet setting

txnelson — Mon, 25 Oct 2021 18:21:16 GMT

Remove the double quotes around theFile