Creating an excel with copy paste and customized header

Help Activities
1

Hi All,

I want to automate a process in which I have to copy the content from web and then create an excel dynamically and paste that content in this excel, I am able to do this with keyboard shortcut activities but I also want to add customized headers for all the columns pasted in the sheet. Please suggest how can I write headers for all the columns in the sheet

Thanks in advance

2

Hi

There are two options

If you want to create a table from website you can do with table extraction where u can still make headers as you want

Or

You can edit the column headers after you it’s been created in your own way by simply using a Assign activity
dt.Columns(“yourcolumnname”).ColumnName = “your new name”

For this first read the excel file and get the datatable
Use this assign and write back to excel using write range activity

For more details on datatable

Cheers @sgarg

3

Thanks @Palaniyappan Preformatted text, But the excel which we have created doesn’t has any Columname, so what should I write in “yourcolumnname” and another thing I want to add column name for about 10 column so how should I iterate it, please help

4

If u don’t have a column name you can even use index position or it like this
dt.Columns(column index).ColumnName = “your new name

Where column index usually starts from 0 for first column

And if you want to do for many Columns have all the column names as a list variable named list_columnname

And also have. A counter variable created with default value as 0 which is of type integer

Now use a FOR EACH loop where pass the list variable as input and change the type argument as string

Inside the loop use a assign activity like this
dt.Columns(counter).ColumnName = item.ToSting

Where item is the variable from loop

Next to this assign activity within the same loop use another assign to increment the counter

counter = counter + 1

This will update all columns

Cheers @sgarg

5

@sgarg

Just a small adfition to what is mentioned above…counter needed not be incremented…for loop has an index property, when a integer is assigned to that property that integer is auto incremneted for each loop…so can use that instead of increment

One more way is have an excel with columns as required already filled and write the data to the same excel and when you write make sure to unchecks headers option…this way we can just use the prefilled template always which has the column names already

Cheers

6

Hi @Palaniyappan

Please check my steps below:

  1. used Read Range activity and saved the created excel in datatable named dtfile
  2. Used Assign activity and added value of List as “new List(Of String)(new String(){“heading 1”,” Heading 2",“Heading 3”})"
  3. Created a Counter variable with Default value 0
  4. used for each loop in which used Assign activity with following syntax
    dtfile.Columns(Counter).ColumnName = currentItem.ToString
  5. then Assign Activity to Counter = Counter +1
  6. After for each loop used Write data table to excel

But in this case when I am writing value of datatable to excel it is appending the datatable to already existed excel values instead of overwriting the already added values so values got added twice and heading got added after already added values

Please help

7

Then I would recommend to delete the at existing file with DELETE FILE activity before writing to excel and then try using WRITE RANGE activity so that it looks fresh and nee

Hope you r not using APPEND RANGE activity as this will possibly happen with that activity as well

Cheers @sgarg

8

Thanks a lot, it helps me a lot

1 Like
9

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