We found a problem with some content when opening excel file (using Output)

I've seen a few posts about this error message but nothing that I could use as a solution.

I'm outputting to an excel sheet and specifying that cell range to output to. I'm not sure if it's worth noting that the range I specify in the workflow is much larger than the actual range as I don't ever have the exact same range each time I output.

I've tried creating a completely new Excel file, and I've tried using both .xlsm and .xlsx. It always opens correctly the first time I run and open the worksheet (after creating a new file to use). And it looks just how I need it to look. But anytime I run again and open I get the error message.

I've tried running from both Designer and from the gallery.

Any ideas?

Output

Accepted answers

All comments

DavidP

When you specify the range, are you using the notation filename.xlsx|||Sheet1$A1:E100 or something like that? I assume you're not writing to a named range in Excel.

The number of columns is always known, right? So that can be exact, like in the example above, there are 5 columns A to E.

If you don't know how many rows of data you're writing, you can just omit the number after E so that it becomes filename.xlsx|||Sheet1$A1:E - this will only create rows to the end of the dataset. Perhaps specifying a different cell range to the dataset you're writing is causing the error.

SSimunov1

I have been having the exact same issue. None of the recommendations to fix it that I've come across have worked.

AnthonyChow

Do you have any special character in your path, e.g. ' ? if yes, remove them.

AnnaQihua

I have same issue. None of the recommendations to fix it.

CK_G

Has a solution been found to this? I have the same issue and none of the suggestions on this thread or any similar threads work for me.

rhubard

Same issue here. Has anyone found a solution? Thanks.

i0r003k

I have had the same issue (without the named range- just outputting to a named worksheet) on multiple workflows for over a year now. Like everyone else, none of the suggested solutions I have found have worked.

TheOC

hey, anyone who received this issue, are you able to supply a workflow file that causes this? Happy to have a deep dive but a hard one to replicate.

Cheers,
TheOC

apathetichell

@TheOC Check out this one if you haven't already...

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Input-Data-Tool-V-String/m-p/757815#M182677

benmillea

I don't know if you ever got to the bottom of this one, but I had the same error message on a file I was outputting yesterday. After a bit of messing around, I figured out that it was down to a VLOOKUP formula that I had accidentally left in one of my sheets that I was overwriting. I copied the data in that field and pasted as special values (text) over the top to remove the formula. When I run the workflow and open the spreadsheet now, I don't get the error message. Do you have any formulae in the fields that you are trying to overwrite?

Mohak

Hey guys,

I was able to resolve the same issue which was causing an excel error.

The sheet I was trying to read had an embedded object. So instead of reading the whole sheet, when I read only the rows with data- it solved the issue.

the object in discussion was a box with text added in it

DawnDuong

hi @crtakacs

Just checking if you have done further processing to the Excel output file?

For example:

- data is output to Table "datatable" in tab "Data". Then the "datatable" drives a pivot table on another tab?

- data is output to Table "datatable" in tab "Data". Then formatting to added to this table e.g. highlight header rows, colour coded etc...?

I found that doing the above causes the issues mentioned in your post and that can be fixed by selecting the option to preserve the formatting in excel (https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Excel-output-to-formatted-sheet/td-p/558978#:~:text=You%20can%20now%20Preserve%20Formatting,in%20the%20output%20file%20path.).

Dawn.

GideonM

First i tried writing to existing sheet with a range but did not work, later I had to specify/create a new sheet in excel without providing range. This worked for me, no more error. Hope this helps in any way

Error statement (writing to existing sheet):

...............".xlsx|||'ManaulRateCheck$L1:P1'"

Solution statement (writing to new sheet):

..............".xlsx|||SpotRateCheck"

amadra

I had the same issue. After 1 hour of troubleshooting, it seems like the issue is the way Alteryx writes to the sheet name.

Old sheet name: "Regulatory Capital-30daysMetric"
New sheet name: "Regulatory Capital 30 days"

Although, just changing the sheet name didn't resolve the issue. I created a new output file with the same file name (deleted the old one) and wrote to the new file (& new sheet name) and it worked!

CFRugh

Having the same issue. Has anyone found a solution to this problem?

BobbySweaters

Another issue Alteryx stopped resolving. Sounds like they're done. Thanks!

VP29

IF you write into a template: make sure that the data types in the template for each column (and row) is aligned (ie values are numbers, headers, titles, referential data are text in the template file). Then using special characters is ok - as long as you use V_WString

knhopper

I've been running into this issue lately, and finally found a fix that worked for me. I changed the File Format to "Microsoft Excel Legacy (*.xlsx)" as opposed to "Microsoft Excel (*.xlsx)" and that seems to have resolved things. Sharing in case anyone else comes across this post looking for help.

Raj_007

Alteryx version 2023.1

tried multiple options: removed the range to use like $A1:Y(100) - removed 1000 still same error

sheet name has a space like "In Progress"

no special characters in the full path - there is a $symbol for the shared drive location

specified overwrite sheet or range - checked keep formatting - not sure why this error throws only few times - some times no error

apathetichell

@Raj_007 - your excel file should be:

path\{filename.xlsx}|||`sheet$range`

NOTE - ` is the key when dealing with sheets with special chracters (including space). not ' - not " - `. also - your range is Y500 - not Y(500) - right?

Raj_007

Hi, Thank you - I have followed the below link - at least i did not see the error now - will monitor it for few more days

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Preserve-Formatting-on-Overwrite-Does-not-work-with-Named-Range/td-p/914226

Raj_007

Hi, i am confused with your note

NOTE - ` is the key when dealing with sheets with special chracters (including space). not ' - not " - `. also - your range is Y500 - not Y(500) - right?

did you mean to use single quotes or the ` - this is the one that is avaialble with tilde sign key on the keyboard - i did not use this

I had to create 2 formula columns

SheetName = 'DataExtract.xlsx|||In Progress$A1:W' +toString([Count]+1) - this is using single qutotes

Path = 'Shared Drive Location\' + [SheetName] - this is using single quotes

in the output

attached screenshot

Capture.PNG

apathetichell

YES. I meant to use the ` which is adjacent to the number 1.

Check this out -> open an excel file in input data. I don't care what the file is- but choose a sheet.

add an action tool.

examine the file location in the action tool. you will notice that the sheet range combo is wrapped in file|||`sheet$range`

agreed?

Raj_007

Hi, I tried but was not able to configure on how to use the action tool

apathetichell

your action tool hooks into an input data - you look at file value for the excel file you are using. That's it. if you can't do this - just trust me- ` is key - not ' - not" - `

Raj_007

You are right - when i look at the file value - it is like this

"T18_35_11+0000.xlsx|||`Sheet3$`" sheet is between the `` these are not for sure single quotes but the entire file value is in between " " double quotes

Capture.PNG

tinio_carmela

I encountered the same issue but when I removed all the existing links/connections and formulas in the input excel file that I'm using, it worked for me and I was able to open the output file without error message.

giorgos_katsou

New to Alteryx and just faced this problem. I think the solution is to change the Sheet name to be less than 31 characters which is the maximum allowed length for an Excel sheet name. This in my case solved the problem and I also made some testing and the problem showed up only when the sheet name was more than 31.

Quick Links

This months top contributors

FláviaB 262

caltang 177

DanM 167

IraWatt 157

gawa 157