Overwrite excel data with write range

Help
1

Hello,

How to overwrite data in excel file using write range ?

Cheers

2

Hi @maroua301

Just simply use Write Range activity then the existed data will be overwritten.

1 Like
3

@wusiyangjia

Sure, but when the existing data is more than the new one, it overwrites only the part with the new onces…

4

Hi @maroua301

If you want clear existing data at first, how about below steps.
1.read range to get a data table
2.use Clear Data Table activity to clear data
3.use the cleaned data table overwrite back in your excel.

5

@wusiyangjia

I can’t use clear data, because the data is in a existing excel file, i have to overwrite the data inside that file after the transactions is finished…

6

@maroua301,

You have two options if you dont want to delete existing row that excel contains.

you could do ui automation or write macro for it.
I would prefer macro it’s stable than ui automation.

Thanks,
Pankaj

7

Hi @Pankaj.Patil

Thank you, but i just need to overwrite the data existing on an excel file…

8

@maroua301,

please follow below steps,

  1. Open excel in application scope,
  2. Select all data(Ctrl + A), Copy it(Ctrl + C),
  3. Paste it new file or sheet(Ctrl + V)
  4. Use Save workbook activity to save data.

Thanks,
Pankaj

9

@Pankaj.Patil

Thank you, but i need to use the write range, the only problem i have, is that when the new data is less the old one, it overwrites only the part wich is equal to the new one counts…

@ppr some help with this issue please ?

1 Like
10

@maroua301
in such a scenario a technique is:

read reange of old existing data
use the Datatable.Rows.Count for appeninding empty rows to the newer less count data
(can quickly be done with a linq)
then write range also deletes unneeded existing data

but in general it is about deleting existing data before writing new data. Any method or cleaning the worksheet is helpfully (CTRL+A. DEL) Activities like delete range, Balareva Delete Sheet…

11

U can simply delete the file (if exists) before writing it (before write range)

12

Before using the Write Range activity, you can use Write Cell activity to clear the sheet. For example,

Sheetname=“Sheet1”
Range=“A:Z”
Value=“”

You can increase the clear area by changing the Range setting.

1 Like