help: Automate: open , refresh excel sheet, save, close

hi all,

I have 3 excel sheets set power query to connect to Sharepoint lists and I manually refresh to get the latest data.

Since I have an alteryx workflow and want to schedule it to run on a daily basis, I want to set an alteryx workflow to automate refreshing the excel files too. I am sure there is a way to automate the opening refreshing and closing of excel files through alteryx.

can someone pls guide?

Workflow

Accepted answers

KGT

FYI, the "Event" setting in Alteryx that will allow you to have the files refreshed before run, is in the "Workflow Settings". It will be the 4th tab on a standard workflow, called Events. That will allow you to execute the Run Command option that @flying008 mentioned.

Any combination of ways from there. All of these will work.

PowerAutomate just refreshes the files daily and your Alteryx process is scheduled for 20 mins later each day.
Powershell or PowerAutomate for the refresh.
Either use that code in "Run Command"/"Event" or have the code in a bat/vbs file and call that.

A "normal" process in Alteryx deals with the data in the process, what you are doing here is dealing with an external file, not the data (well, eventually the data, once that file has it). Hence the extra steps.

All comments

OTrieger

@Tanvi_Khannaa
Alteryx is not opening the excel file, therefore a source file will never going to be impacted by Alteryx, unless you will write the data into it.

You might be able to do it with Python tool or Run Command, but I do not think that Alteryx will be the best program for that, Power Automate will be a better choice, or VBA.

flying008

Hi, @Tanvi_Khannaa

1- You can use PowerShell code in RunCommand tool to get your want.

# Import the Excel module
Add-Type -AssemblyName Microsoft.Office.Interop.Excel

# Define the file path
$filePath = "D:\abc.xlsx"

# Create an Excel application object
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false # Set Excel to be invisible

# Open the workbook
$workbook = $excel.Workbooks.Open($filePath)

# Refresh all queries
$workbook.RefreshAll() 

# Save the workbook
$workbook.Save()

# Close the workbook and Excel application
$workbook.Close()
$excel.Quit()

# Release COM objects
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
[System.GC]::Collect() 

Write-Host "Data refreshed and saved successfully!"

2- Another way use Run-Command in Event with Before Run option.

Add-Type -AssemblyName Microsoft.Office.Interop.Excel; $excel = New-Object -ComObject Excel.Application; $excel.Visible = $false; $workbook = $excel.Workbooks.Open("D:\abc.xlsx"); $workbook.RefreshAll(); $workbook.Save(); $workbook.Close(); $excel.Quit(); [System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook); [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel); [System.GC]::Collect(); Write-Host "Data refreshed and saved successfully!"

Tanvi_Khannaa

since I want to schedule alteryx workflow for daily automation for all other manipulations, I was wondering if I can set alteryx to schedule the refreshing of excels before I run the workflow too.

so - I need scheduled automation to refresh excels and then these excels are inputs to alteryx workflow (that will be scheduled) and publish to tableau server. so end to end automation is the goal.

- havent explored power automate but can we schedule through that and new file can be downloaded into the set folder for alteryx to auto-pikcup?

- VBA: I trired recording to open refresh and close but not sure whow to schedule for automating it. also the recording dint work

flying008

Hi, @Tanvi_Khannaa

1- havent explored power automate but can we schedule through that and new file can be downloaded into the set folder for alteryx to auto-pikcup?
A: Of course you can.

2- VBA: I tried recording to open refresh and close but not sure whow to schedule for automating it. also the recording dint work
A: Compared to using VBA to refresh Excel data connection, it is more flexible to use the above PowerShell code with before run options to control refresh in Event of Alteryx.

KGT

Any combination of ways from there. All of these will work.

PowerAutomate just refreshes the files daily and your Alteryx process is scheduled for 20 mins later each day.
Powershell or PowerAutomate for the refresh.
Either use that code in "Run Command"/"Event" or have the code in a bat/vbs file and call that.

Lada_R

Hi community.

I have a similar situation - need to refresh an excel file before reading. I used Events with Power Shell code written by flying008. The issue is that the code runs correctly in Power Shell when executed manually, but when in Alteryx, it gives "Error 1" (too generic, not specified error). Did anyone have the same? How do I distinguish whether I have wrong configuration, or limited permissions? Thank you!

jrlindem

@Lada_R I recommend opening a new question/post and referencing this thread for context. This way it'll get more attention from those who may be able to help as an "unsolved post." -Jay

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703