Write to Excel - Over write specific range.

[jkell]

Hi - I used this post to write multiple outputs from a single workflow to individual sheets in excel.

But I am wondering if the tool can be configured to overwrite only specific ranges within each tab (the same range for each tab).

I am able to do this when configurating the Output Data tool ("C:\\test.xlsx|||Sheet1$a10:b20" ) when I am not writing to multiple tabs.

But when doing using dynamic method provided in the post, it is not working as expected, and each sheet is overwritten entirely each time. 

Any thoughts? Thanks!

[apathetichell]

see the bottom dropdown on your output data - "change entire path" has to be selected. you can then send in your full filepath with:

filename.xlsx|`sheetname`$range send into your output data.

[jkell]

the original post says to use "change file/table name"

I tried updating to "change entire path" but now I am getting a output error "Error: Output Data (73): You must specify a sheet name."

But as you can see in the screen shot, I do have a sheet name designated 

[kboeckholt · Accepted solution]

@jkell You're almost there - It looks like you have Cust ID selected at the bottom. I'm assuming you want each sheet in the output to use the Cust ID as the sheet name. Since you're now changing the entire file path, the field selected here needs to contain a full path instead of just the sheet name. 

You'll want to create a new field which contains the full file path where you want the data to be output:

Then select that new field in the Output Data tool and uncheck the box for "Keep Field in Output"

Hope that helps!

[jkell]

@kboeckholt Bingo! That did the trick, thank you!

[SkyDe]

Hello,

Do you know why I'm having issues with a .xls file? I have done an overwrite and preserver formatting for .xlsx and it has worked, but I can not get it to work for .xls, please see the attached screen shots.

[KGT]

Hi @SkyDe, you're better off posting as a separate question to get the eyes. Often this will only be seen by the people in this post and only if they log in and check updates to old posts.

The solution may be that you need to qualify the sheet name with 'Sheetname$'. This used to be the method used but is not required anymore. I'm guessing it may still be needed for XLS files in some situations. The XLS and XLSX are written using entirely different drivers and so could act differently. The last time Microsoft used XLS was pre 2007 and they have stopped "supporting" it since, so the XLS driver might need older techniques.