Best way to copy entire sheet contents from excel when sheet name is unkown

Help
1

Hi all,

We would like to copy the entire contents of an Excel spreadsheet preferably without opening the spreadsheet in the foreground. Can this be done please? Also instead of using “Sheet 1”, is there a way to make this name a wildcard as the name of the sheet will change (its usually filename + date).

Thanks,
Tony

2

Yeah, you can use the Excel Scope and you can uncheck the “Visible” property if you want.
To the get the sheet name, you can use a WorkbookApplication variable, which can be used in the Output property of the Excel Scope.

Let’s say you create a variable called wb and have it in the Output property of the Excel Scope. Now, put in a Read Range and for the sheet name use the wb variable:

wb.GetSheets(0)

.GetSheets will return the list of all the sheets. So using the index (0) will return the first item in the sheets. If the sheet you want is not always the first sheet, then you would need to query the list of sheets. For example:

wb.GetSheets.Where(Function(sh) sh.StartsWith(filename) )(0)

However, simply using wb.GetSheets(0) or wb.GetSheets.First will usually work if all you want is the first sheet.

Let us know if you need further help.

Regards.

2 Likes
3

Do you know how the GetSheets method works? Does it return the first visible sheet? Is it the order of the sheets as the user would see? Or is it based on how they’re ordered when you look in the excel VBE?

4

Hi @Dave
This is just from my own understanding, but each Sheet has an index which would also be referenced in vb/VBA. And, the Sheets are always ordered by that index which is also the order you see yourself when you open the file. If you move the sheets around, the index changes for that sheet.

So, ya it’s in the order you see when you open the file.

Selecting the sheet you want would require some querying or just some ForEach logic, but query is more efficient.

5

Do you konw how hidden or very hidden sheets are handled? Are they simply not found when using .GetSheets?

6

Thanks a lot Clayon, that worked a treat!

Now all we need to do is copy the data and paste it into another Excel spreadsheet - would we use the variable and a write line activity in the new open application Excel activity to do this?

7

Just tested it and hidden sheets are still seen. Only problem I know of is if there are Charts as one of the sheets, then it fails. There is an alternative vb method if there are Charts potentially though.

1 Like
8

np @TRX
You could probably store the sheet name to a variable (so you can close the other Excel file using Close Workbook using the wb variable). Then, just use another Excel Scope with the Write Range to output the data stored from the Read Range and using the sheet name that you stored to a variable. —you can choose to use the Workbook Write Range too if you prefer which won’t open an Excel insance

9

Thanks, that makes perfect sense and it worked! Cheers for all of your help!