topic Re: Jsl error when reading excel file that is open by excel :: There was an error opening the file. in Discussions

Jsl error when reading excel file that is open by excel :: There was an error opening the file.

joshua — Fri, 09 Jun 2023 18:14:15 GMT

Hi,

I was wondering any work around this. The case is I have my windows and excel file is open that I edit and save. Meanwhile, I try to open that excel file with jsl for further data processing (the *.xlsx still open with Excel while I'm doing this.)

When I try to open that file I get this error

"There was an error opening the file."

Is there any work around this ? This excel file is shared excel file so it's most likely would be open by another user and another computer so I hope jsl has solution to work around this ?

I am using JMP 14.0.3

Thanks

回复： Jsl error when reading excel file that is open by excel :: There was an error opening the file.

frank_wang — Fri, 25 Mar 2022 03:13:07 GMT

Open(
	"D:\ATS\frankwang\Desktop\Test.xlsx", // Here is file path
	Worksheets( "Sheet1" ), // Worksheets Name
	Use for all sheets( 1 ), // Check seting use for all sheets or not (1 -> Y, 0 -> N)
	Concatenate Worksheets( 0 ), // Check need concatenate work sheets or not
	Create Concatenation Column( 0 ), // if you want to concate, this will be set
	
//Below setting about Data Table of sheet. Such as Column Header, Number of rows in hearder and so on
    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( "-" )
	)
)

For detail, can check in Index Script and search 'Open'

Re: Jsl error when reading excel file that is open by excel :: There was an error opening the file.

ih — Mon, 28 Mar 2022 13:36:02 GMT

Hi @joshua,

You could copy the file to a temporary location and open it from there.

Names default to here(1);

fn = "$Sample_Import_Data/Bigclass.xlsx";
tfn = "$TEMP/tempfile.xlsx";

copy file(fn, tfn);

Open(
	tfn,
	Worksheets( "Bigclass" ),
	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( "-" )
	)
);

delete file(tfn);

Re: Jsl error when reading excel file that is open by excel :: There was an error opening the file.

hogi — Sun, 06 Aug 2023 12:00:27 GMT

Is there an option to check if the file is opened in Excel ?

Is there an option to load the Excel file in a way in Jmp such that it's OK that the file is opened in Excel - e.g. with read only flag?