Alter Excel Document

[p4uk80 (Praveen)]

Hi,
is this possible using UiPath:

I need to modify my excel worksheet using UiPath.

1. Lets Say I am having 10 sheets in my workbook and I need to delete all blank sheets.

2. In every worksheet I am having a row ‘No Data’ and I have to apply a color to ‘No Data’ column and merge the columns with rest other. The row number is not fixed and it can be anywhere.

Output File:

Input file

[Filip_C]

Hi @p4uk80,

You can invoke some VBA on that spreadsheet.
Here’s a working demo:
Alter Excel Document.zip (8.4 KB)

• remember to enable programmatic access to VBP What does 'Error 1004: Programmatic access to Visual Basic Project is not trusted' mean? : PC*MILER

Hope that helps you!
Best Regards,
Filip

[p4uk80 (Praveen)]

Thanks, that’s very helpful @Filip_C. Also I am trying to delete sheets having blank Cell D22.

Few of the sheets from workbook are having blank cells and I want to delete all those.

Sub TestCellD22()

'Test if the value is cell D22 is blank/empty
If IsEmpty(Range(“D22”).Value) = True Then
MsgBox “Cell D22 is empty”
End If

End Sub

[Filip_C]

Hi @p4uk80,

Your VBA code should work just fine once you change msgbox to a delete method
Though I’d pass the current worksheet and reference it → ws.Range(„D22”).Value
(And use ws to call delete as well)

Please let me know if you have any issues with that … if not then please mark my response as a solution

Best Regards,
Filip

[p4uk80 (Praveen)]

Below line is working fine if Sheet is completely blank however I want sheets having D22 cell as blank should be deleted.

Pls suggest what change required here :

If WorksheetFunction.CountA(ActiveSheet.UsedRange) = 0 And ActiveSheet.Shapes.Count = 0 Then
    
    s.Delete

[Filip_C]

Hi @p4uk80,

then

If IsEmpty(s.Range(“D22”).Value) Then
s.Delete
End if

should do the job
hope that helps!

Best Regards,
Filip

[Filip_C]

Hi @p4uk80,

Have you had any additional issues with the above ?

Best Regards,
Filip

[p4uk80 (Praveen)]

Thanks @Filip_C. It was giving some exception so I attempted below and it worked:

If WorksheetFunction.CountA(ActiveSheet.UsedRange) **<200** And ActiveSheet.Shapes.Count = 0 Then
    
    s.Delete

I will check the ‘ISEmpty’ method and will tell you the issue, if found.

Many thanks for helping me out here.

[p4uk80 (Praveen)]

Getting error here:

Pls suggest