How to Copy a column in Excel

Help Studio
, ,
1

I have an Excel spreadsheet that looks like this :

image
image1521×345 18.7 KB

I have attached my workflow.
Cable_Capitalization_Copy_Order_Excel.xaml (14.5 KB)

What I intend to do is to click on the first cell in the A column, send a Keyboard shortcuts:
1 . shift+ctrl+down
2 . ctrl+c
This is the view of my Workflow:

Cable_Capitalization_Copy_Order_Excel
Cable_Capitalization_Copy_Order_Excel976×2037 138 KB

However whenever the process opens the Excel, the it shows the bottom of the table. Because, of this, the process errors out at the Click activity that is supposed to click on the cell A2.
Can someone help? Also, is there any other way to copy the values in a column in Excel. This is because I need to copy and paste it in another application, the whole column.

2

Hi,

You can try iterating through the rows in data table.

3

Hi @RobinsonFrancis

First we create a list variable where can can store the strings, along with that we will read the excel & store the data in datatable

image
image789×651 27.4 KB

Then we will iterate through that datatable & add all the order nos in a list variable using append items to List
For you to copy the data to other application we will convert the list to string & store it on clipboard
image
image715×659 29 KB

You can paste the data in any application using the clipboard just like i pasted in notepad
image
image641×618 27.9 KB

Find attached the workflow for your ref
Copy_Excel.zip (69.3 KB)

Hope this helps :slight_smile:

4

Hi @RobinsonFrancis ,

We would avoid UI Actions as much as possible as there are already activities that could work in the Background for your case.

Maybe a fewer steps to follow for implementation :

  1. Read the Excel Sheet using Read Range Workbook activity with Add Headers enabled, get the Output as Datatable, say DT.

  2. Next, we can get the Order column data using the Expression below with the help of Assign Activity :

ordersList = DT.AsEnumerable.Select(Function(x)x("Order").ToString).ToArray

Here, ordersList is a variable of type Array of String.

  1. Next to Copy the Values as is present in the Excel, we Join each value using NewLine like below :
copiedColumnData = String.Join(Environment.NewLine,ordersList)

Here, copiedColumnData is of the type String.

You can then use the copiedColumnData variable to paste the value in another application.

1 Like
5

You don’t use UI automation (ie click, etc) to work with Excel files. You use the Excel activities.

6

You can try selecting the A1 cell first and then continue with your next actions.

.i.e.

  • Use Excel application scope and open excel
  • Use excel Select range activity with A2…control goes to A2.
  • Add your keybord shortcuts for copying the whole column

Again this is only if using a string variable does not work and you want to use a clipboard.

If these steps work pls mark the solution as accepted.

7

Thanks [AJ_Ask], it worked. I ran it using my own file as well.

1 Like
8

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.