Check if an Excel Sheet (tab) is blank!

Help Studio
, , ,
1

Hello all,

I have multiple sheets in an excel file.

I have a datatable which needs to be written to a new tab in the excel file.

How can I check if the sheet is blank or not? (not the file itself), based on which I can either write or append data.

Cheers!

3

Hello @nvora, :smiley:

I created a short solution for your problem. Check if my workflows below help you!

:point_right: This workflow checks in which position of the sheet it can insert its DataTable: Example.xaml (7.4 KB)

:point_right: This workflow goes through your Excel sheets and checks which one is empty to input your DataTable: GetSheets.xaml (10.2 KB)

4

Hello @nvora

This can be easily achieved by checking the count of rows in the datatable.

You can loop through each sheet and read it using Read Range activity. Then use DT1.Rows.Count.

Where DT1 is the datatable from Read Range activity.

Thanks

1 Like
5

HI @nvora

Here are the complete steps

  • Use Excel Application Scope give your excel path

    • Get Workbook Sheets and store it in the variable called Sheets or whatever (Change the scope of the Variable created)
      image
      image1328Ă—254 12.7 KB

  • Kill Process with the process name “EXCEL” and enable Continue on Error and keep a delay of 2 seconds after the kill process
    image

  • For Each item in Sheets

    • Excel Application Scope and give your input path
      • Read range give the sheet as item.ToString and in the properties panel disable Add Headers and the store the datatable in a variable called DT
        image
        image1331Ă—471 23.7 KB

  • Then DT.Rows.Count will return you the count

Regards
Sudharsan

6

Thank you for the reply.

But this solution will not work as the sheet will not be created by then and the Read Range will throw an Exception.

I need to check this before writing it for the first time.

7

Will not throw you an error it will read only the created sheets @nvora

8

I want to check before this sheet is created for the first time.

The read range will work only if the sheet is already created.

Basically check if the sheet is created for the first time then → Write with headers, else → Append.

I hope I am able to explain it correctly.

9

SO do you know the sheet name that is to be created right?

You can try like this

After getting the sheets from get workbook sheets

use if condition String.Join(“,”,Sheets).Contains(“Your New sheet name”)

If true → You need to give the steps that need to be done if the sheet is available already
if False->You need to give the steps that need to be done if the sheet is not available already

Regards
Sudharsan

10

okay then you can try this way @nvora

1 Like
11

Yes, I can try this.

Will let you know if worked.

Thank you for your help!

Cheers!

1 Like
12

This project uses Modern design and was already developed by someone else.

I cannot use Excel application scope in Modern design and cannot change it to classic.

I need to figure another way of doing this.

13

If you go to filter you can see show classic @nvora
enable that and use classic activities

Regards
Sudharsan

14

image
image363Ă—614 10.2 KB

@nvora

1 Like