How can I import data from a hidden Excel worksheet? If I use Open() with the option Worksheets("TechnicalData"), then it fails as long as the worksheet "TechnicalData" is hidden in my Excel file. As soon as I unhide the worksheet in Excel, the import works again.
(Note: Why do I want to import a hidden worksheet? Because I need to import data from a "user friendly" excel sheet, which is not actually machine readable and positions and labeling of cells can change. To make the import work reliably, I created an additional worksheet "TechnicalData", which has named references to the "user friendly" sheet. For user friendly behaviour, I would like to hide this sheet.)
I think this is a great question, and one that you'd think there would be an answer for. I tried it with some test Excel files and scripting, but I couldn't find a way to make JMP get around not "seeing" the hidden tab. I tried multiple work-around attempts and couldn't get it to work. You might have to save a local copy with the tab unhidden to get around this and then run your JSL script on your local xlsx file rather than the shared one. Not an ideal situation, but at least you could continue with your work.
It's likely something on Microsoft's side that won't let JMP see the worksheet.