Workflow to open Excel files, apply filter and then close

Question

Hi All,

I'm wondering if you could help me. I have a workflow which runs daily and provides data for some external vendors. The reports are exported to a SharePoint site, which the vendors can then access using 2FA.

We can't allow the vendors to have edit or download access for security reasons, so they have view only access. Unfortunately this means that they can't apply filters to the spreadsheets themselves, however if I add a filter to the spreadsheets myself, they are able to see them and interact with them.

What I'm wondering is, does anyone know whether there's a way to have Alteryx open these excel files, apply filters to all sheets (literally Ctrl+Shift+L) and then save and close? There's 5 files generated a day in slightly different formats.

Thanks!

Steven.

ChrisTX · Answer

This information may be helpful:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Run-Command-to-delete-data-from-excel-sheet/td-p/355790

You can use PowerShell commands via the Run tool to manipulate Excel sheets. Example site is

By Example – PowerShell commands for Excel

https://sqlnotesfromtheunderground.wordpress.com/2014/01/25/modifying-excel-documents-with-powershell/

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Alteryx-and-Powershell/ta-p/31418

execution of PowerShell scripts is disabled by Windows as a default. To enable execution run powershell.exe with admin privileges and run 'Set-ExecutionPolicy Unrestricted' to allow execution of all scripts. For details see:

https://docs.microsoft.com/en-us/previous-versions/windows/it-pro/windows-powershell-1.0/ee176961(v=technet.10)

See: Tool Mastery | Run Command
https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Tool-Mastery-Run-Command/ta-p/31548

Scheduling a Workflow with a Powershell Script

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Scheduling-a-Workflow-with-a-Powershell-Script/td-p/312130

Q: please provide a sample workflow to copy a file from one folder to another folder using alteryx (Kind of taking backup of files).

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/How-to-copy-a-file-from-one-folder-to-another/td-p/91171

running Powershell scripts using an EXECUTE command from the AX server

On the AX server, create a folder named "Desktop" under one of these trees:

32-bit operating system: C:\Windows\System32\config\systemprofile\
64-bit operating system: C:\Windows\SysWOW64\config\systemprofile\

Automating PowerShell tasks that use Excel

https://sqlnotesfromtheunderground.wordpress.com/2014/02/15/automating-powershell-tasks-that-use-excel/

If you need to automate a PowerShell script, either with a SQL Agent Job or the Windows Task Scheduler. you will properly run into the issue of: running the script via ISE or PowerShell works. But when you try to automate it running it fails. I found the solution near the end of this post

http://social.technet.microsoft.com/Forums/windowsserver/en-US/aede572b-4c1f-4729-bc9d-899fed5fad02/run-powershell-script-as-scheduled-task-that-uses-excel-com-object?forum=winserverpowershell

Chris

sjpostlethwaite · Answer

Thanks, I'll take a look into this.

ChrisTX · Answer

One option would be to use Alteryx to write out a Powershell (.ps1) file (or manually create the ps1 file), then in Alteryx under the Developer palette use the Run Command tool to run the Powershell script.

There a lots of examples available online showing how Powershell can control Excel.

Chris