I am trying to automate a process that reads in several xlsx workbooks. The sheet names on these workbooks change periodically so I cannot hard code the sheet names. Is there a way to get the sheet name for each of these files?
Answer:
Assuming I understand your question correctly, you have a directory with multiple workbooks with different sheet names. For example:
Workbook1.xlsx|||SheetOne
Workbook2.xlsx|||SheetTwo
And the names of these workbooks and sheets may change. I would use the directory tool in conjunction with a macro that iterates through these to grab the sheet names.
In the macro import a list of sheet names on an xlsx file with two controllers attached- one to replace the file path name, and the second to replace the ‘<List of Sheet Names>’. In the main workflow you can join records by position and you will have the full file path and sheet name for every file in the directory.
