Copy Excel File with Dynamic Sheet Name

Help
1

I have an Excel file with dynamic sheet name. this sheet name changed every week (sample: emp_status_report_6132018)… date changed.

Now I need to copy this file but the read range needs the exact sheet name.

I have found the solution to get the sheet name and assign to a variable then put it in the read range.

The issue is processing time takes 44 seconds to complete. While when I put the exact sheet name without passing the variable it just takes 2 seconds.

How can i improve the processing time?

Thank you.Copy File With Dynamic Sheet Name.zip (21.1 KB)

1 Like
2

I would say fastest processing time would be to store all Sheets into an Array, then either filter it or sort to get the sheet name you need.

Let’s suppose you want to get the sheet name that has “status_report” in the name and with the latest date…

First to get all sheets (and assuming they are Worksheets, not Charts), you can use an Excel Scope with a WorkbookApplication variable, we’ll call wb.

wb.GetSheets

That is an array, so you can use .Where and other functions to get the one you want.

wb.GetSheets.Where(Function(s) s.ToString.Contains("emp_status_report").ToArray

That will get all sheets that contain the status_report words. If the sheets are always in order, then you can just take the Last one by doing this:

wb.GetSheets.Where(Function(s) s.ToString.Contains("emp_status_report").ToArray.Last

However, it might not be in order, which in that case you would need to sort it.

wb.GetSheets.Where(Function(s) s.ToString.Contains("emp_status_report").ToArray.OrderBy(Function(s) Date.ParseExact( System.Text.RegularExpressions.Regex.Match(s.ToString,"[0-9]{7,8}").Value.PadLeft(8,"0"c),"MMddyyyy",System.Globalization.CultureInfo.CurrentCulture)).Last

So, now I used .OrderBy, and inside I parsed out the date with a padding of 8 characters to make it like “06132018” so the date parse works. It sorts by the date, then using .Last takes more up to date sheet name.

I hope this answers your challenge.

Regards.

3

Hi ClaytonM,

I did used the GetSheets function… please see my project in the attachment in my first post.

However I used workbook.GetSheets(0).

The excel file only contains 1 sheet(emp_status_report_6132018)

4

this is now solved! i used wb.GetSheets(0).ToString() in the read range.

thank you!

1 Like
5

Hi ,

Take a look this will help you.

Regards
Balamurugan.S