cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Submit your abstract to the call for content for Discovery Summit Americas by April 23. Selected abstracts will be presented at Discovery Summit, Oct. 21- 24.
Discovery is online this week, April 16 and 18. Join us for these exciting interactive sessions.
Choose Language Hide Translation Bar
Steffen_Bugge
Level IV

Open function with relative file location

I need to use the open function with a relative file location. The following code finds the location, and stores the path to the file in the variable RelativeLocation

 

relativePath = Get Default Directory();
UPtwo = Left( relativePath, Contains( relativePath, "/Folder/", -2 ) );
RelativeLocation = UPtwo || "Excel_file.xlsx";

However, when using the Open function with the RelativeLocation variable, the script crashes. Here is an example:

Open(RelativeLocation,
	Worksheets( "Summary" ),
	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( 3 ),
		Data Starts on Row( 4 ),
		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( 0 ),
		Suppress Empty Columns( 0 ),
		Treat as Hierarchy( 0 ),
		Multiple Series Stack( 0 ),
		Import Cell Colors( 0 ),
		Limit Column Detect( 0 ),
		Column Separator String( "-" )
	)
);

This gives the following error message: 

  

Steffen_Bugge_0-1693640843832.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
David_Burnham
Super User (Alumni)

Re: Open function with relative file location

Perhaps start off by putting in a couple of diagnostic lines prior to doing the open:

show( RelativeLocation );
show( FileExists(RelativeLocation) );

 

-Dave

View solution in original post

2 REPLIES 2
David_Burnham
Super User (Alumni)

Re: Open function with relative file location

Perhaps start off by putting in a couple of diagnostic lines prior to doing the open:

show( RelativeLocation );
show( FileExists(RelativeLocation) );

 

-Dave
Steffen_Bugge
Level IV

Re: Open function with relative file location

Thanks a lot, @David_Burnham !

show( FileExists(RelativeLocation) );

gave a negative answer, and 

show( RelativeLocation );

indicated that the path was incorrect. So, after fixing the fault, everything works as intended!