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.
(Bruna Cavalcanti Bruno)
I created a short solution for your problem. Check if my workflows below help you!
This workflow checks in which position of the sheet it can insert its DataTable: Example.xaml (7.4 KB)
This workflow goes through your Excel sheets and checks which one is empty to input your DataTable: GetSheets.xaml (10.2 KB)
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.
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)
Kill Process with the process name “EXCEL” and enable Continue on Error and keep a delay of 2 seconds after the kill process
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
Then DT.Rows.Count will return you the count
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.
Will not throw you an error it will read only the created sheets @nvora
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.
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
okay then you can try this way @nvora
Yes, I can try this.
Will let you know if worked.
Thank you for your help!
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.
If you go to filter you can see show classic @nvora
enable that and use classic activities