This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
It's the most wonderful time of the year - Santalytics 2020 is here! This year, Santa's workshop needs the help of the Alteryx Community to help get back on track, so head over to the Group Hub for all the info to get started!
When you use the output tool, you should be able to output to an existing Excel file and choose the tab for your target. You may not (NEVER NEVER USE THE WORD CAN'T) have that excel file open if you want to read or write to it.
Once you select the Excel file, you'll be prompted to specify the table (or worksheet) name. You can choose #3 your output options to: Overwrite Table (drop), Append Existing, Delete Data & Append, or Create Table. If you want to append data, you can do that and not touch the existing rows. The next option in the configuration #4 is the append field map (click the ...) and you'll see if your field names match the Excel sheet field names.
If everything is kosher, you'll be in business. I like to start from scratch and create a table from Alteryx (my preference) and write to a sheet named: RAW_DATA. That's my cup of tea and I hope that you'll agree with my logic. I use the RAW_Data table and generate a fine pivot table from it. In Excel I set the pivot table options to refresh on OPEN. Now, when I put real data into the file the pivot table always shows the new data as part of the pivot without requiring a user to refresh the data.
I then HIDE the RAW_DATA tab and it appears as though I've refreshed the pivot from Alteryx. Almost as smooth as if I had used Tableau (which is a great option).
If you need more guidance, please let us know (maybe provide a sample set of data and a pivot table) and I or someone in the community will help you further.
Alteryx ACE & Top Community Contributor
Chaos reigns within. Repent, reflect and reboot. Order shall return.
Using two files is not an option. I could do that, output to CSV and then have Excel with Power Query connections to an external file but I would have to send the two files to my colleagues which is not feasible.
I realised that I think it's only when Excel file has Data Model built-in then Alteryx must be doing something incompatible with Excel that causes it to break. If I give Alteryx the same output file it overwrites the worksheets correctly.
Another thing is that if I only leave Excel file with Power Query connections, it overwrites the worksheets but it looses the table names which are referenced in Power Query.
Any help to make the Excel<->Alteryx integration tighter would be appreciated!
Hey @jdunkerley79, what if I wanted to output Log files to the same location-- does your process allow me to retro-actively name the previous file (e.g. "ErrorLog-[Time stamp]") so I can accumulate files without repeatedly overwriting the same one?
EDIT: Also, I keep getting a 'Error Opening file: specified path not found' error, although the email attachment is successfully emailed. Is there something I could do to reduce errors like this or is it a non-issue because the app is still working as expected?
I currently do a very similar process with my daily reports. Input the data into Excel and have all my pivots set to automatically refresh. That works well...most of the time. I noticed that when I open the Excel Document on a mobile device, the pivots DO NOT refresh.
I think the work around on this would be to open the file and save it. Is that something the Alteryx could do? If I add that step in my workflow, it would basically be refreshing the pivots before sending out the report via email and therefor mobile device users would also see a refreshed pivot.