How to Write to Excel CELL index v.2

excel
activities

#1

Hi!

In the Excel activity Write Value you the Range object is default at “A1” How can I adress the cell index instead - like the below VBA code: Range(“A1”) = 1 Cells(1, 1) = 1 Range(Cells(1, 1), Cells(1, 1)) = 1

It would be really helpful to know it.
Thank you in advanced,

Alba


Get the location of a variable from excel
#2

Hello Alba,

one way I access the cells using write value dynamically is as follows.

create a variable num1, and assign a value to that variable dynamically and append that value to the range
eg : num1= 2
then range will be “A”+ num1
so this will make it dynamic and you can access any cell in excel and also it will be helpful while looping through the cells .

Thanks
Bhanu Prakash L


#3

Hello,

Thank you for the answer but I need to have the column also as a variable.
I want to pass the column as a number and then I want to know how to get the letter (in Excel) of the number given.
Maybe there are snippets which can do that, but I’ve looking for and I haven’t found anything useful yet.

Thank you again!
Alba


Reading Colums from A to Z and how to iterate them
Excel内を検索して置換 Search and replace in Excel
#4

In this case create an array of string variable Columns = ("A","B","C",..., "AZ").

When using Write Cell you will set the cell to
Columns(columnIndex)+rowIndex.ToString

`Columns(0)` will return "A"
`Columns(1)` will return "B"
and so on

Everything is parametrized now.


How to get a cell number from the value in the excel sheet
#5

Hi,

Any chance we’d be able to get R1C1 style addresses or an activity to translate between them in the future?

Regards.


#6

Hi!

I haven’t, the array is an optional solution but not what I wanted at all. We definitely need another activity to address cells in relative references. I will try to figure it out and in case I do, I will share it ;).

Regards.


#7

Sorry for posting on a solved thread, but I am encountering a problem, I am trying to write cell to an existing excelsheet in a specific column for each row, in the “for each row” activity. I tried to define the cell in “write cell” activity as ““D” + row.ToString” (trying to write to column D), but there is a - Write Cell : The range does not exist Exception.


#8

Try "D" + (yourDataTable.Rows.IndexOf(row) + 1).ToString


#9

I assigned the row to a variable before writing it in the write cell activity. So Assign - MyVar = Row().tostring
Then in the write cell activity “D” + MyVar.
Hope that helps


#10

Actually I set up a variable called rowCount that went up by 1 every time the loop went through. It was a generic var not a string. then I added it to the write cell activity “D” + Rowcount. didn’t wwant to give anyone a bum steer


#11

In-case if I have some hidden rows, how can I get the IndexOf the row?


#12

Then I will re think, why you need to have the rows hidden, because it will be much more simpler to have the data cleaned before hand.

I’m guessing if you use filter will do the work, if not you can work with data tables, or you can invoke a method like:

I guess you can iterate throw them inside the excel activity, again not tested yet.

Dim hasHiddenRows As Boolean

 For Each rows In dtTest
 If rows.Hidden Then
            hasHiddenRows = True
            Exit For
        End If
    Next

regards


#13

Thanks, Since my Client have the excel with some hidden rows, and they use filters. So is there any method in which I can directly take the row number instead of taking from datatable? So that it will be very helpful.


#14

Thanks you solution is help me!!


#15

Hi,
I tried the above syntax “A”+ num1, but it’s showing like “disallows implicit conversions from string to double”.
What is the type of the variable that is to be stored.


#16

It’s operator disambiguity solving.

Since you need a string, and you’re doing an addition + operation for string and double, double takes precedence (hence the error message that string is not implicitly convertible to double, because it isn’t).

You need to either use "A" & num1 or "A" + num1.ToString("F0") .
& operator in VB.Net will implicitly call .ToString() for primitive types. Do note that it will take default formatting, so for Double type it may not be what you want. Works great for Int types though.
F0 format specifier is for fixed decimal precision of 0, meaning no decimal precision (since there can’t be half a row offset in Excel).


#17

Invoke code activity with colNo Int32 In param and colName String Out param; the code:

colName = (Chr(64+(colNo\26)) & Chr(65+colNo-(colNo\26)*26)).Replace("@", "")

Should work for colNo starting from 0 (C style) while colName has maximum two characters (about 676 columns)


#18

May i ask if the array of string variable “columns” is assigned this way
Columns = (“A”,“B”,“C”,…, “AZ”)

because i keep getting an error below:
image