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

[Tanvi_Khannaa]

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?

[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]

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.