"Write Range" not writing values or giving error

[Btrumps]

Hopefully you guys can help me out with this one. I’ve tried breakpoints and a couple debug methods but to no avail.

My automation reads a range (4 rows of values) from one spreadsheet, then needs to write it to a second spreadsheet in a specified row (“C” + (masterDashboardDT.Rows.IndexOf(row) + 1).ToString). I’ve checked this value and it correctly shows as C180, which is the cell I would like to write my values.

However, I do not get any errors, and my finalized spreadsheet looks like this:

Any help would be greatly appreciated!

[sarathi125 (Parthasarathi)]

Hi @Btrumps,

Is your write range activity and the excel application scope inside a For each Row?

[Btrumps]

@sarathi125 Yes, this is what it looks like:

[sarathi125 (Parthasarathi)]

So it is not writing for any of the row in that loop, could you please put the “valuesToCopy” into a output datatable and check it in a message box. I am suspecting that is not holding correct value and hence not writing properly

[AshwinS2 (Ashwin S)]

Hi @Btrumps

Give the Range as “C1:C”+dtMaster.rows.indexof(row+1).ToString

Thanks
Ashwin S

[Btrumps]

The values appear to be correct, I believe?

[Btrumps]

@sarathi125 basically, the first datatable is arranged alphabetically, the second datatable is not. i get the name from the first datatable, then use for each row to loop through and find that name in the second. once found, I want to paste the values from the 1st datatable into the 2nd one, at the row found

[bcorrea (Bruno Correa)]

One tip for you is, if the excel is the same, dont put your excel scope activity inside the Loop, but the other way around, so it wont get open and closed so many times… Also check if the Scope has autosave enabled or put a save workbook after writing the value…

[Btrumps]

@bcorrea Got it, I did that here:

I added a message box for “C” + (masterDashboardDT.Rows.IndexOf(row) + 1).ToString

And it appears to show the correct cell to start writing the values. Not really sure what’s going on here…

[Btrumps]

Also, autosave is enabled, I tested with “Save Workbook” and that didn’t fix

[bcorrea (Bruno Correa)]

Wait, if you are trying to write a cell, why you are using write range?

[Btrumps]

@bcorrea I have to write the information in 4 cells, C180, D180, E180, F180. The valuesToCopy variable is from a Read Range that read from 4 cells B2, C2, D2, E2 in a separate spreadsheet.

[Btrumps]

It takes it from here:

and saves the read range as “valuesToCopy”

[bcorrea (Bruno Correa)]

but if you pass c180 in the range, you are writing a cell and not a real range… ranges are like “c180:f180” or table names, named ranges…

[Btrumps]

@bcorrea Thanks for clarifying that for me. So I changed my “Write Range” to:

“C” + (masterDashboardDT.Rows.IndexOf(row) + 1).ToString + “:” + “F” + (masterDashboardDT.Rows.IndexOf(row) + 1).ToString

Which is essentially “C180:F180” in this example. Still, nothing is being written in this sheet. I’m very confused, cause I’m unsure how to debug this and figure out exactly what’s going on.

[bcorrea (Bruno Correa)]

If you are getting no errors, then your variable may be empty…

one more tip: The For Each activity have a property INDEX so you dont need to use: masterDashboardDT.Rows.IndexOf(row)

[Btrumps]

@bcorrea Alright, so a bit of an update… I replaced the original “Read Range” to 4 “Read Cells”, storing each value as a variable. I then used 4 “Write Cells”… and it works!

This is a bit excessive though, I’m unsure why my Read Range didn’t work with “B2:E2” and not get written to “C180:F180”

I was hoping to just selected these 4 cells from one spreadsheet and copy paste them into a row in the second spreadsheet

[bcorrea (Bruno Correa)]

well i guess read range will give you a datatable, write range if used in the middle of another table would be strange, so i think you are better of with separate cells

[Btrumps]

Thanks for all the help @bcorrea! I’m still gonna brainstorm and see if there’s another method of selecting those 4 cells through one activity and pasting them using another, could simplify the automation and make it a bit more elegant.

[bcorrea (Bruno Correa)]

no prob, i mean you can still use Read Range to dtVariable, but use 4x write cell like this: dtVariable.Rows(0)(0),dtVariable.Rows(0)(1)… and if you will do that a lot, you can extract that part as new workflow file and invoke it passing parameters.