Hi @Debbie Edwards ,
Thanks for using Microsoft Q&A forum and posting your query.
Unfortunately, reading sheet names dynamically is not supported currently.
If you know the sheet names upfront, then you can create pipeline parameters and dataset parameters and pass the value from pipeline parameters to data parameters. Also if you have the file names and sheet names stored in a database table or a csv file, then you can also use a look up activity to retrive those values and pass them to the dataset parameters dynamically.
Please refer to this related stackoverflow thread and see if that fits your requirment. - Dynamic sheet name in source dataset: (Excel (Blob storage)) on Azure Data Factory. - Error: Please select a work sheet for your dataset
Here is an existing feature request from ADF user voice forum submitted by other users - Get list of Excel sheet names. I would recommend you to please up-vote and comment on it to help increase the priority of the feature request.
Hope this info helps.
----------
Please don’t forget to Accept Answer
and Up-Vote
wherever the information provided helps you, this can be beneficial to other community members.