How to insert row in excel sheet using MS Excel VBO.

sunny

New Member
Hi,

Could you please help on how to insert a row in excel sheet in blue prism? Thanks in advance for your help!.

thanks,
Sunny.
 

VJR

Well-Known Member
Hi,

Could you please help on how to insert a row in excel sheet in blue prism? Thanks in advance for your help!.

thanks,
Sunny.
- First use the 'Select' action to select a row at the place where you want to insert.
For eg; If you want to insert at the 3rd row then in the 'Cell Reference' parameter provide as "3:3".
If for blank rows between 3 to 5 then "3:5"
- Then use the 'Insert' action with 'Shift' parameter as "Up".
 

sunny

New Member
Hi VJR,


Thanks for the reply. I tried suggested option but I could see only one cell is inserted instead of a row. Could you please help me on this?


Thanks,
Sunny.
 

VJR

Well-Known Member
Hi VJR,


Thanks for the reply. I tried suggested option but I could see only one cell is inserted instead of a row. Could you please help me on this?


Thanks,
Sunny.
Have you given a cell reference like A3 instead of 3:3 as stated above?
 

vandede

New Member
Use the following VB.Net code to create a new VBO:

GetWorkbook(handle, "").ActiveSheet.Range(cellref,cellref).EntireRow.Offset(0,0).Insert

for this you need handle and cellref as input parameters.

Don't forget to add a Page Stage with the "Activate Workbook" VBO
 

Nalluriprasad

New Member
Dim worksheet,Excel,ExcelApp As Object

Excel = GetWorkbook(handle,Nothing)
worksheet = GetWorkbook(handle,Nothing).ActiveSheet
ExcelApp = Excel.Application
worksheet.Rows(RowNumStart & ":" & RowNumEnd).Select
ExcelApp.Selection.Insert

Inputs:
1.RowNumStart
2.RowNumEnd
3.handle
No output required.
 

VJR

Well-Known Member
Select (single row):
View attachment 1535433900762.png

Insert action:
View attachment 1535434583912.png

Go to Cell:
The go to cell is done to set the focus on any other cell to remove the below horizontal selection line that Excel creates after inserting a row

View attachment 1535434237040.png

Input:
View attachment 1535433923770.png

Process Diagram & Output:
View attachment 1535434181752.png

View attachment 1535434315134.png

After Go to Cell focus in placed in cell A1 to remove the horizontal selection
View attachment 1535434269613.png

Insert Multiple rows:

View attachment 1535434472045.png

View attachment 1535434445089.png
 

kkps8796

New Member
Bro ,
I need one help.
I have large data in excel.Total column A to z with data and total row is 15.
My requirement is insert the column in between particular column with title name. For example column V name as "percentage" before percentage I need to insert one column but i did hard code, my manager said don't do hard code use some logic to get result.
Please help me for this.

By,
Kishore kumar R
 

janu

New Member
Hi All!!Hope you are doing good. I am trying to insert dynamic rows to excel. But sometimes i am getting error.

I have a excel file with two sheets. I have to filter for specific record from the Sheet1, check for duplicates and I have to paste in sheet2. The second sheet(Sheet2) has 36 different sub tables with diff number of rows and column. In each table I have to paste values( which I filtered from Sheet1).
In certain cases, during run time I have to insert additional rows to the tables in Sheet2. So i am using the action select and insert.

STEPS Followed:

1.Open instance.
2. Open Workbook
3. Get worksheet has collection (Sheet1) - Collection1
4. Filter for specific criteria and save it to a final Collection. So I have got 36 collections to paste it in 36 tables in Sheet2.
5. Get Worksheet has collection (sheet2)-Collection 2
6. Looping the collection 2 to get the row number of Table 1 & Table 2 so I have the counter values which specifies the exact starting row number for table 1 & table 2. with these counters i can get the row difference. saving the row difference between table 1 & Table 2 in a data item (RowDiff)
7. Logic used: if collection1 rows are empty no need to write values, if collection1 has values then get number of rows to paste & check whether need to insert additional rows.
8. If collection2 has got less rows and if I want to insert rows i am actually getting the number of rows to add and trying to select the cell and using Insert action. For eg: The starting cell of table1 & 2 is 8 & 28. Row diff is 20, i need to write collection with 30 rows so i have to insert ten extra rows. I am trying to select the number of rows to insert extra rows. using activate worksheet>>Select (Specifying cell refence as 27:37)>> Insert (tried using "Shift Up" & Entire Row">> write collection (specifying cell reference as A8"

The challenge here is between table1 and table 2 there is a comment section row which is a merged row. So sometimes my insert option works and sometimes its not working and I am getting the below error

Exception : Failed to delete selection: This selection isn't valid. Make sure the copy and paste areas don't overlap unless they are the same size and shape.
I have attached the screenshot of the excel which exactly looks like the one i am working. And also the screenshot of the stages.

Counter 2 - Starting cell of Table1
Counter 3: Starting cell of Table 2.
 

Attachments

  • 73485FD3-DCC4-4F89-B90B-9E5BBE47C8F2.png
    73485FD3-DCC4-4F89-B90B-9E5BBE47C8F2.png
    19.5 KB · Views: 28
  • 724ABFE3-A5FA-4374-93BE-B3C8228C0248.png
    724ABFE3-A5FA-4374-93BE-B3C8228C0248.png
    19.5 KB · Views: 26
  • 6CAC5C69-5DF7-4B8B-9470-DF1205BC8230.png
    6CAC5C69-5DF7-4B8B-9470-DF1205BC8230.png
    11.2 KB · Views: 28
  • 65F4A3C3-29CD-4EEE-B665-EA4D13AA304F.png
    65F4A3C3-29CD-4EEE-B665-EA4D13AA304F.png
    74.9 KB · Views: 29
  • 687E6894-BA55-43C4-B7CE-99AE98B02346.png
    687E6894-BA55-43C4-B7CE-99AE98B02346.png
    13.2 KB · Views: 25
  • 7B5A7798-360D-4BBE-BD2F-1DD6D619B30A.png
    7B5A7798-360D-4BBE-BD2F-1DD6D619B30A.png
    15.5 KB · Views: 28
Top