Error: "Data found beyond specified range"

[AkisM]

Been having some trouble with writing to specific cells lately, so I created a super simple workflow with just an input and an output to test it.

I'm attaching below the workflow along with the output that it's writing to. I have defined 2 named ranges in the excel sheet

 Title1 which is A1:D1 and Title2 which is A2:D2. For whatever erason, title2 range works whereas title1 range1 doesn't, says there's "data beyond the specified range", when there isn't. Can someone help me understand why this happens?

Additionally, when configuring the output tool to "skip field names" (which is very useful) it always breaks the excel file and shifts data down and also misses the last record of data. Any help with that?

[fmvizcaino]

Hi @AkisM ,

For that to work, you need to be careful with your range, meaning that you have to specify a row size that contains all data from your dataset. Since you have 5 rows, you need to use A2:D6.

Also, if you want to skip the header, you will need to modify your range to start one line below, A3:D6

Best,

Fernando Vizcaino

[AkisM]

Hi @fmvizcaino , thanks for the help. However have you tried replicating the solution you are suggesting? The excel still breaks if I try to skip field name and output to A3:D6 as you suggested

  

[fmvizcaino]

Hi @AkisM ,

Yes, I've replicated the solution. I just forgot to talk a little about the breakage.

That is something that happens to me frequently but not always and I'm almost sure it is a bug, but it is not present in the known issues in release notes, so maybe I'm mistaken.

https://help.alteryx.com/release-notes/designer/designer-20201-release-notes

Best,

Fernando Vizcaino

[AkisM]

There's never been a time I tried to output to a specific range with skip field names on and the excel not breaking. And as we can see above, it occurs even in the most simple excel file, so it is bound to occur in bigger more complicated excel files too. Unfortunately keeping field names in is not an option. Does that mean I have no way of outputting data to a pre-formatted excel at specific ranges without the excel breaking and shifting data/deleting data? Anyone has a solution for this?

[fmvizcaino · Accepted solution]

@AkisM ,

If you want to skip names and also preserve formatting, that is a known bug.

But you can preserve your formatted excel without breaking it if you keep your field names as well.

Then I suggest you to use a excel macro triggered by excel opening to format only the header while the bug is not fixed.

https://support.office.com/en-us/article/automatically-run-a-macro-when-opening-a-workbook-1e55959b-...

[AkisM]

Thanks for confirming @fmvizcaino . I guess for now i'll just have to add a row above my target data to accomodate the field names, and I'll use a select tool to change the field names to spaces to make it look like the row is empty.

However, since you brought it up, what am I supposed to be using in my output in such cases? Ovewrite sheet (drop) or overwrite sheet or range? It's worth noting that I'm using version 2019.3.5.1794 so I don't have the overwrite range option, just asking for future reference. Though I'm guessing overwrite sheet (drop) was the same thing. I suppose the bug is present in both versions.

[fmvizcaino]

Hi @AkisM ,

You need to use overwrite sheet or range option, it is the only way of preserving an excel sheet template without the need of triggered macro in excel.

Best,

Fernando Vizcaino

[Doug_B]

Thank you for asking your question.  I have had nothing but trouble trying to preserve Excel formatting.  I have never gotten it to work.  Generally all I want to do is skip the header, and dump my results into the lines following the header, regardless of range.

[Labhesh]

HI @fmvizcaino

I am using the sae method for excel workbook. Giving the range and using the overwrite sheet and range setting. 

whenver i am running for the 2nd ot 3rd time it show me the same error.

Is their ay way to resolve it ?