Excel To Table Tool

[JoeBorgione]

The Excel To Table tool is really handy, especially with various drivers being all over the map between 32 bit ArcMap and 64 bit ArcGIS Pro & MS Office 2016 blah blah blah...

At any rate, when one deploys to the tool, it is smart enough to provide a list of worksheets that are within the excel file you enter as the first argument.  (See attached image).  I'd like to add the tool to a script tool; is there a way to mimic the intelligence of the original tool in my script tool and get the list of possible sheet names?

[DanPatterson_Retired]

Which begs the question Joe ... why not use the tool itself

If you are producing your own incarnation, then it would be an optional parameter which is 'dependent' on the input excel file.  I don't know if 3rd party files allow for dependency like you can do it when you want to select fields from a featureclass table.  

You might want to show how your toolbox tool parameters are set up

[JoeBorgione]

I have some excel worksheets that hold table definitions for a new geodatabase. I have worked out workflow such that if I get the excel worksheets into a table, I can then iterate through the the table, and add those fields to yet another table, rather than copying and pasting or worse yet re-entering field names and types into a new table. At this stage of the project, the Excel table definitions are subject to change.

At any rate, just after my initial post, I added the Dependency to the in_excel_file, but that's not quite enough:  in the Data Type column I added Data Element.  It's not as slick as the tool itself, but it does allow the user to navigate to the in_excel_file and pick the appropriate worksheet. 

[XanderBakker]

The tool validation could list the sheets in the Excel file and populate a dropdown box:

import xlrd
excel_file = r'C:\GeoNet\ExcelInTBX\myExcelFile.xlsx'
xls = xlrd.open_workbook(excel_file)

sheet_names = [sheet.name for sheet in xls.sheets()]‍‍‍‍‍