Excel leave only headers at first row and remove all other rows

Hi,

How can I open excel file and leave only headers which are at first row? Which activities can I use to delete all rows except headers?

Use Excel Application Scope to open the Excel file.
From there, do a Read Range on the entire sheet, with the Output to variable “mysheet”.
Then use the Delete Range activity, set the Range property to “A2:Z” & mysheet.Rows.Count. This will delete everything after row 1 within columns A through Z.
You can change Column Z of course if your data goes beyond that.

1 Like

Not sure this will work, but try read the sheet into a data table (with headers) and then use clear datatable, then write dt (with headers) back to excel file.

One easy and fast way would be to use Invoke VBA activity with this little code:

Sub DeleteExceptFirst()
Rows("2:" & Rows.Count).ClearContents
End Sub

I see you posted invoking VBA code a couple times recently. Could you share an sample image of the activity in UiPath and specify if it belongs inside a scope activity, etc.? Thanks in advance.

image

and that code go inside that script.vba file.

1 Like

@bcorrea
Hi,

Thank you for your response!
I created process as you advised, but when robot use Excel Application Scope activity Excel file opens and showing message:
Workbook message
After it robots stops with this error:
Invoke VBA

  1. MS Excel 2013: Display the Developer tab in the toolbar.

@amanda.smith
Thank you for your response!
I created that process, but in “Delete range” activity in “Input” section:
Range
there is Error: “Rows” is not part of “UiPath.Excel.WorkbookApplication”

@bcorrea
it will not help, because excel files are store in shared folder and each time excel file will be new.

How strange. I’ve been using those same steps for a couple processes I’ve been running. Do you know if your Excel package has been updated?

image

You have the option of setting that in trust center for all files…

do you see any message when navigating to “!” sign?
Rows_mess

1 Like

Sorry yes, I pulled this from the process I’m currently building and I don’t have that activity connected yet. In looking at the last one I completed, it looks like you just need to add .ToString to the end of it.

image

@amanda.smith
Seems “Rows” belongs to Datatable. Do you indicate DataTable in “ReadRange” activity?

@amanda.smith
I created a process a bit in another way. The flow is:
‘Excel Application Scope’ → ‘Read range’ indicating ‘Output’ to Datatable → ‘Assign’ Variable: ExcelRows=MyDataTableName.Rows.Count → ‘Delete range’ indicating in Range field:“A2:Z” & ExcelRows.ToString
and it worked!

1 Like

Thank you all!
@amanda.smith showed me right direction

1 Like

HI @Apple1,

Here is the solution…

input :
image

Output :
image

Workflow design:

Sample Project :
DeleteData.zip (50.5 KB)

Regards
Balamurugan.s

here all rows deleted? from A2?

@MitheshBolla In these kind of situations if you are trying to keep a header as a template, then better to keep a template file which can be reused in all the execution. Deleting the rows can take sometime based on the number of rows.

2 Likes