Output - Overwrite Specific Columns, Not Entire Sheet

[Lauren_Holmes]

Hello,

I am trying to produce an output from Alteryx, that only overwrites the data in certain columns. The reason being the end columns contain some previous formatting in Excel. I have attached an example output. 

From this example, I would ideally like Alteryx to only overwrite columns A - F with the data.

My flow and output will always contain the same amount of columns, so if I specified A-F there would be no issues of it having extra columns added later down the line. I am unable however to determine the number rows in the output as this number will change frequently.

Please may someone advise if this is possible, and explain how I do this? I have tried using Overwrite Sheet or Range and specify which range but it won't work.

Thank you in advance.

[atcodedog05]

Hi @Lauren_Holmes 

You can set the range only as column range. This way row range is dynamic/infinite (i,e not be worried about)

Snap:

or

Hope this helps : )

[binu_acs]

@Lauren_Holmes  Add a Count Record to count the number of records then use a formuila tool to create the path and the range if the records are dynamic

[ERK]

If I understand the ask you need to write to a range, but don't know the whole range ahead of time due to the number of rows in the data. If this is correct you can:

1. Have the file name as part of your data.
2. Modify the file name once you know how many rows there are.
   1. Specify the file name, for example: \\PathToFile\FileNameWithExtension|||'WorksheetName$T3:AF6'
   2. T3:AF6 is a range. Have a function to modify this based on how many rows are in your data (start row + count of rows in the data - 1 if column headers are not to be written)
   3. Note: The $ must be there - it is not part of the range itself
3. Have the OutputData tool take the file name from the input.
   1. Check Take File/Table Name From Field
   2. Select Change Entire File Path from the drop down
   3. Uncheck Keep Field in Output unless you want the file name in a column of your data
   4. Specify the field in the input that contains the file name from step 2.

[Lauren_Holmes]

Hello, 

Thank you. When I do this however, it works, but the old records do not get removed:

My issue is, i don't know how many new rows there will be each time it is ran - sometimes there will be more, and sometimes there will be less.

Is there a way to have it so it removes these extra rows?

[atcodedog05]

Hi @Lauren_Holmes 

Write to a large range like below that way all the value below will also be removed. 1,048,576 rows is max allowed rows in excel.

Hope this helps : )

[Lauren_Holmes]

Would it be possible for you to amend this flow please? I have added 99999 onto the end of "F" but I still cannot get it to remove the other rows from previous run. 

[Lauren_Holmes]

Hello,

I have tried running using this method, but keep having this error come up:

Is there something else i need to do?

[atcodedog05]

Hi @Lauren_Holmes 

Reduce the range by 5 or 10 or 50 it should work 😅

[atcodedog05 · Accepted solution]

Hi @Lauren_Holmes 

Dont do filepath change thing. Just set the workflow like this. This will work

Hope this helps : )