How to delete Excel sheet data without Excel activities

[Tech]

Hi, I dont have excel installed in my machine. And I have an excel file that already has some data, I want to clear all the data from this excel file. How this can be done without using Excel Activity pack.

[vrdabberu (Varunraj Dabberu)]

Hi @Tech

If you have your excel in a data table variable you can use Clear Data Table acitivity. This acitivity will clear the data keeping only the headers.

Regards

[lrtetala (Lakshman Reddy)]

Hi @Tech

[Tech]

This doesn’t work, also I need to clear headers also. I want to clear all data from that excel so that it will be an empty one.

[vrdabberu (Varunraj Dabberu)]

Hi @Tech

After Read Range Workbook try using this in Assign acitivity:

yourDataTable = New DataTable()

This should help you with creating an empty Data table.

Regards

[lrtetala (Lakshman Reddy)]

@Tech

Can you try below code

Code:

Dim excel As Microsoft.Office.Interop.Excel.Application
Dim wb1 As Microsoft.Office.Interop.Excel.Workbook
Dim ws1 As Microsoft.Office.Interop.Excel.Worksheet
excel = New Microsoft.Office.Interop.Excel.ApplicationClass
wb1 = excel.Workbooks.Open(WorkBookPath)
ws1 = CType(wb1.Sheets(SheetToDelete), Microsoft.Office.Interop.Excel.Worksheet)

' Clear both data and headers in the worksheet
ws1.Cells.Clear()

wb1.Save()
wb1.Close()
excel.Quit()

ws1 = Nothing
wb1 = Nothing
excel = Nothing
GC.Collect()

Cheers!!

[Yoichi]

Hi,

How about using ClosedXML as the following? This works without Excel. (However requires UiPath.Excel.Activities package for ClosedXML or install CloseXML independently)

Using wb As New ClosedXML.Excel.XLWorkbook(filename)
Dim sheet As ClosedXML.Excel.IXLWorksheet = wb.Worksheet(sheetName)
sheet.Clear()
wb.Save()
End Using

Sample
Sample20240118-1a.zip (7.0 KB)

Regards,

[Tech]

@Tech

This doesn’t work, also I need to clear headers also. I want to clear all data from that excel so that it will be an empty one.

Thanks Yoichi, it worked!