Workflow to open Excel files, apply filter and then close
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Labels:
- Developer
- Output
- Run Command
- Sharepoint
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks, I'll take a look into this.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
This information may be helpful:
You can use PowerShell commands via the Run tool to manipulate Excel sheets. Example site is
By Example – PowerShell commands for Excel
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:
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
Q: please provide a sample workflow to copy a file from one folder to another folder using alteryx (Kind of taking backup of files).
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
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
Chris
