This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Hi, I need to read, on a daily basis, one Excel file. The file name is fixed, the columns are fixed and the file contains only one sheet. The problem is: the sheet name contains the number of records on the file, and change from one day to the other day. For example, possible names are 'Rows 1 to 1232', 'Rows 1 to 5429', 'Rows 1 to 15',.... Although I can't say I have an infinite number of sheet name options, it's a pretty big list.
If your Excel file is an XLSX then you can simply use the 'Read Sheet Names' option in the Input tool to get the sheet name and use this to construct a file path.
If your file is an XLS (which I'm guessing it probably is) then the 'Read Sheet Names' function doesn't exist for this file type, so the solution will require a bit more creativity.
One option is to write a batch script which you can run from a CMD tool within your workflow to invoke a VBA function to rename the sheet directly in your source Excel file (or a copy if you need to preserve the original file). This would require a bit of coding along these lines: