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

Question

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

hogi · Answer

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?

ih · Answer

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";<BR />
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);

frank_wang · Answer

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'