ArcGIS Pro not recognising worksheets in a spreadsheet

[LachlanMilne1]

Hi 

I am having a difficult time getting ArcGIS Pro to recognise multiple worksheets in an xlsx spreadsheet.  For some reason catalog does not recognise all worksheets within a spreadsheet (see black below), whereas ArcMap has not difficulty (see white).  When I try to use the 'excel to table' tool in Pro I get the error message, whereas the same tool works correctly in ArcMap.  

Is there a way to force ArcGIS Pro to recognise all worksheets in a xlsx spreadsheet?

[DanPatterson_Retired]

beyond the generic recommendations here

Work with Microsoft Excel files in ArcGIS Pro—Excel | ArcGIS Desktop 

Did you add the whole spreadsheet first? then try to select the sheet from the contents ?

[LachlanMilne1]

Hi Dan - Thanks - No, but when I tried this approach only one worksheet was visible.  I have also tried saving a copy of the spreadsheet - same issue arises.  I am going to start with a new spreadsheet and try importing the data to it.  This problem with worksheets not being visible in spreadsheets has happened with other spreadsheets, so it is not just this one.  Also maybe there are merged fields or other issues with the spreadsheet I am not aware of that are preventing it showing up in ArcGIS Pro, but that must be unlikely because I could access the worksheet and proecess it in ArcMap with no problems.

[DanPatterson_Retired]

Could be something in the spreadsheet.  Could be the location of the file.

Could be just about anything.  If that spreadsheet doesn't work and others do, and others don't, you need to put the does and don'ts into some common category that is the root problem.

On that note, check out the help tips in the link I sent, which I am sure you are aware of.. but there may be a glimmer you may have missed (hidden this, split that, links anything, etcetera... that foul spreadsheets allow are generally not in arc*world)

Also tomorrow morning a 7:00 AM, I am releasing a blog post on Excel use and data issues when working with it and ArcGIS Pro

If you must work with spreadsheets, the rules are strict.  Anything that allows for mixed data types has even more rules.

Arrays of a uniform data type are best… then it doesn't matter what goes into the rows/columns/cells (ie images)

Arrays of mixed dtypes have to keep the rows or the columns of one data type, then you can stack them row-wise or column-wise as long as you know what is where and can define it (ie tables in general, like dbase, arc*whatever tables, numpy arrays etc, most other science-used data formats)

Spreadsheets don't have such rules.  You can put anything anywhere.  Maybe that is why everyone uses them.

[NaomiBegg2]

It looks like Pro 2.4 is broken when it comes to Excel. 

I can't open Excel sheets unless they are in CSV.  I can't use CSV as they remove the leading 0's which I need for a join later.

I can add the excel sheet in an older version of Pro but as soon as I try and open the project in the latest version the table breaks.

[DanPatterson_Retired]

Try

Excel To Table—Conversion toolbox | ArcGIS Desktop 

instead of working with them directly since

Work with Microsoft Excel files in ArcGIS Pro—Excel | ArcGIS Desktop 

has too many qualifications to guarantee success

[RichardFiore2]

Thought I had navigated most of the Excel Class V rapids until I tried to do some calculated fields referring to data in Excel sheets.  Finally realized it was the "$"  appended to all Excel worksheet names that threw errors... Excel to Table saved any more hair from being pulled out!  Thank you!

[KoryKramer]

Do you get any warning or error messages when trying to work with Excel, and if so, what do they say?

Note that there were changes in 2.4 noted in https://pro.arcgis.com/en/pro-app/get-started/whats-new-in-arcgis-pro.htm

See: https://pro.arcgis.com/en/pro-app/help/data/excel/work-with-excel-in-arcgis-pro.htm

[DionLiddell]

Admin command prompt then AccessDatabaseEngine.exe /quiet sorted our issues - thanks!