Get sheet names with excel act (modern)

Help Process Mining
1

Hello, I want to extract the sheet names from an Excel file using Excel Modern Activities. I don’t want to use the “Get Workbook Sheets” activity. How can I do this? I need to read the sheet names that follow a specific format (dd.MM.yy) and then save the most recent date.

Forum Engagement Daily Reports
2

Any specific reason for not using the activity designed for the same task?
Another option would be using VBA to get sheets names which I would say reinventing the wheel for which we already have the prebuilt activity.

3

Hey @nkaizer,

Use Excel Process Scope → inside it, drop a For Each Excel Sheet in Workbook activity.

In the loop:


If Regex.IsMatch(CurrentSheet.Name, "^\d{2}\.\d{2}\.\d{2}$") Then
    dateSheets.Add(CurrentSheet.Name)
End If

After loop: latestSheet = dateSheets.OrderByDescending(Function(d) DateTime.ParseExact(d,“dd.MM.yy”,Nothing)).First()

5

@nishiijain2000

Would you please explain the package in which this activity available?

6

because i want to use only excel activities . i want to use excel process scope instead of excel app scope

7

in the loop you put if activity ? for example in condition you put If Regex.IsMatch(CurrentSheet.Name, “^\d{2}.\d{2}.\d{2}$”)
and in then what activity you use?

8

thank you for your reply, where can i found this activity?

9

@nkaizer

Use a simple If + Add to List pattern.

Assume you have a List(Of String) variable called dateSheets (initialized before the loop).

Inside For Each Excel Sheet in Workbook:

  • If activity
    Condition:
    System.Text.RegularExpressions.Regex.IsMatch(CurrentSheet.Name, “^\d{2}.\d{2}.\d{2}$”)

    Then:
    Use Add to Collection activity
    Collection: dateSheets
    Item: CurrentSheet.Name

After the loop, you can get the latest sheet like this in an Assign:

latestSheet = dateSheets.OrderByDescending(Function(d) DateTime.ParseExact(d,“dd.MM.yy”,Nothing)).First()

10

instead of add to collection activity i use Append items to collection (because i cant find the add to collection) . is it the same?

11

@nkaizer

Yes, that is fine use Append Item to Collection

13

thank you for your help. it works !!

14

Hi @nkaizer

Please check the below thread if you are using modern excel activities:

Regards
Parvathy

15 
thank you for your help!!!
16

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.