delete a column from excel

[MadhuG001]

Hi All,

How to delete a column/columns in excel without moving to Collection?
Thanks in advance.

 

[Sachin_Kharmale]

Hi Madhu,

If you want to delete columns from excel
Step 1- add new action MS-Excel VBO As Delete Columns

Step 2.Add Code Stage and give input as Handle and Column Range that you want to delete
View attachment 1561378500793.png
Step 3.use bellow code and then publish a action and call from process studio
View attachment 1561378583893.png
Dim Rng As Object
Rng=GetWorkbook(handle,Nothing).ActiveSheet.Columns(columnsRange).Delete

I Hope it will help you.

 

[MadhuG001]

Hi Sachin,
Thanks for the response.

I did as you advised.
But it's reflecting error as : "GetWorkbook' is not declared. It may be inaccessible due to its protection level"
(Even if I declare the GetWorkbook, it's reflecting error)

& what is Shift = -4131

Please advise.

 

[Sachin_Kharmale]

have you create the new action in blue prism Ms Excel VBO ?
And paste the above lines in code stage ?
also attach errors screen shot so it will be better to resolve issue .

 

[MadhuG001]

I have added a page to MS Excel VBO
Then I have added an ACTION stage for "CREATE INSTANCE" & another ACTION for "OPEN WORKBOOK"
Then in code stage I have given the instance name & inside the columns data item I have given one column name from the execl.

It run successfully, but didn't delete the Column

Please advise.

 

[Sachin_Kharmale]

Madhu Just follow the simple steps -

Step 1- Create new action Delete column form excel worksheet in MS Excel VBO
View attachment 1561384843390.png
Step 2 - Give input parameter to start of action as Handle and Column Range
View attachment 1561384864762.png

Step 3- Add code stage in your action
View attachment 1561384901688.png

Step 4- Give Input as handle and column to delete
View attachment 1561384931323.png

In Code section add the code which is already posted.

step 5- Publish the action from object studio . save and close object.
View attachment 1561384975776.png

Step 6- Open your process , Select Object that you have added new action .
View attachment 1561385034821.png

Create Instance
Open Workbook
Call newly created action and pass input as handle and columns range
Note- if you want to delete single column. eg- delete H column then pass "H:H" or if you want to delete column from C to K then pass it as "C:K"
for testing you can set some data in column that you want to delete so you will get it has been deleted or not.
and close the instance.

 

[MadhuG001]

Thank you so much, Sachin.
It's working now, but we have to add Save Workbook before proceeding to Close Instance.

 

[csmdebasis]

Hi Madhu,

I hope u got your solution, but there is even a simple way out. Just first select the column, and then delete it from the inbuilt VBO provided by BP. For eg. If u wanna delete column B, then in select pass "B:B" and then perform the delete action.

Thanks