Swap Two Excel Cell Values

Devendhar

New Member
Hi Team,

I want to Swap the cell values in Excel Sheet using blueprism.

For Example : I have Data in cell A and B Columns and I Want Swap Cells Values data B to A

Regards,
Devendhar
 

sivagelli

Well-Known Member
You can achieve this either using Code stage or using 'Get Cell Value' and 'Set Cell value' Stages in the process, however this would be little heavy on the steps-

Here is the flow using Get Cell value and Set cell Value actions-
  1. Start
  2. Create Instance
  3. Open Workbook
  4. Activate Sheet
  5. 'Get Cell Value', A. The output will be a Data Item
  6. 'Get Cell Value', B. The Output is a Data Item
  7. Now using, 'Set Cell Value' action set the value of A with the output of the Step 6
  8. Similarly, using 'Set Cell Value' action set the value of B using the output of Step5
  9. Save workbook
  10. Close Instance
  11. End

Post back how it goes!
 

Devendhar

New Member
HI Sivagelli,

Thanks for your response !!

I have 1 lac records in excel sheet and I have tried with above solution but problem is it was taken as more time to complete the process .So i'm looking for Sample code for this requirement.

Could you please share me with Sample code .

Thanks in Advance !!

Regards,
Devendhar
 

sivagelli

Well-Known Member
HI Sivagelli,

Thanks for your response !!

I have 1 lac records in excel sheet and I have tried with above solution but problem is it was taken as more time to complete the process .So i'm looking for Sample code for this requirement.

Could you please share me with Sample code .

Thanks in Advance !!

Regards,
Devendhar
Alright! the easiest way to do this is using Data - OLEDB VBO. I noticed this to be quick.

Lets say you have excel with Column1 and Column2 in Sheet1 and you are looking to swap the values in two columns-

  • Start
  • Set Connection
  • Open
  • Execute
    • Update [Sheet1$] [Column1] = [Column2], [Column2]= [Column1]
  • Close

Refer to the post if you have questions on how to connect to Excel using OLEDB VBO.

Post back how it goes.
 

Devendhar

New Member
Hi Sivagelli,

Thanks for your reply.

I have tried with the solution you mentioned. But I'm facing the below issue.

Internal : Could not execute code stage because exception thrown by code stage: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

another after step into Execute step ,The below error message is coming.

Internal : Syntax error. The token '[Sheet1$]' is invalid. Please check the case of your operators (eg 'or' versus 'OR') and check that your functions use brackets after the function name eg Now(), eg Len("abc").

Regards,
Devendhar
 

sivagelli

Well-Known Member
Internal : Syntax error. The token '[Sheet1$]' is invalid. Please check the case of your operators (eg 'or' versus 'OR') and check that your functions use brackets after the function name eg Now(), eg Len("abc").
[Sheet1$] refers to the name of the sheet, you may have to change the sheet name according to your workbook.


Internal : Could not execute code stage because exception thrown by code stage: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.
Follow the link
If it does not help try to Google for the error and you will be loaded with lot of solutions.
 
Top