How do I extract excel filename from a user-selected file?

[#1]

Folks,

I can't seem to figure out how to extract the original file name of an Excel workbook that a user selects.  After selecting the appropriate file as shown below, the actual file name I get is the worksheet name (sheet 1), not the original file name (test_data.xls). I'm ultimately trying to rename the originally selected excel file and need the file name, not the opened worksheet name, to accomplish this. Any ideas how I can accomplish this?

dt = Open(

  Pick File( "Select Excel File", "$dir_rawdata/", {"All Files|*"}, ),

  Worksheets( "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( 3 ),

  Headers Start on Row( 1 ),

  Data Starts on Row( 4 ),

  Data Starts on Column( 1 ),

  Data Ends on Row( 0 ),

  Data Ends on Column( 0 ),

  Replicated Spanned Rows( 1 ),

  Suppress Hidden Rows( 1 ),

  Suppress Hidden Columns( 1 ),

  Suppress Empty Columns( 1 ),

  Treat as Hierarchy( 0 )

  )

);

fname.old = dt << Get Name;

[#2 · Accepted solution]

Hi,

I've managed to extract excelfile name using the word() function:

for example:

Excelfile = pick file ("Select your CSV File(s)", "", {"JMP Files|csv", "All Files|*"}, 1, 0, "", "multiple" );

For ( [logic],

  SampName = Word( -1, Excelfile , "/" );

  ExcelName = Word( 1, SampName, "." );

  file contents = Load Text File( Excelfile );

//use the file cotents to phrase through your rows

)

the ExcelName will give you the name of your excelfile

cheers

[#3]

The file name is part of the "Source" script. I think this works (assuming POSIX path):

fname.old = Word(-1, Arg(dt << Get property("Source"), 1), "/");

[#4 · Accepted solution]

Thanks folks for your suggestions,

I was playing around and realized I needed to define a data table from the file selected in the Pick File function: dt.select = Pick File( "Select Excel File", ...... Once I did that I was easily able to parse out the file name using the Word function. 

MS, thanks for the clever way of extracting the file name from the Source property.  That was a really clever and learning about the use of -1 Word was very helpful.   

[#5]

Thats a good idea, it all depends on what your script should do. Personally I had the need of reading in several excel files at once, therefore I stored the files into a list and phrased them using for-loops.

Excelfile = {}

Excelfile = pick file ()...

best of luck!