Solved: Re: Help with a Complicated Flow (open Excel, refr...

Chris110 · ‎07-07-2023

Hi everyone

I'm working on creating a flow that does the following:

1. Opens an Excel file in a static location (the file is an Export from a SharePoint list) and click the "refresh all" button

>>> Let's call this "Master File"

2. Save the Master File and keep it open

3. Open an Excel file in another static location (the file location is static, but the naming convention is dynamic - "FileName-0707" this week...and next week, it'll be "FileName-0714")

>>> Let's call this "Sync File"

4. In the Sync File, run 2 macros

5. Save the Sync File.

Any thoughts on whether this is possible? I tried recording but got some mixed results.

Thanks a lot in advance for the advice 🙂

Agnius · ‎07-07-2023

Do not use the UI to refresh the connections in your file. It's unstable. Instead, use the following PowerShell script. We use it all the time for projects where we need to refresh the connections in a file:

# Excel input file
$xlsx = "%InputFilePath%"

# Create Excel COM object
$excel = New-Object -ComObject Excel.Application

# Make excel invisible
$excel.Visible = $false

# Disable excel alerts
$excel.DisplayAlerts = $false

# Open excel workbook
$wb = $excel.Workbooks.Open($xlsx)

# initiate refresh of the connections
$wb.RefreshAll()

# get all the connections in the work book
$connections = $wb.Connections

# for each connection wait while it is refreshing
for ($i = 1; $i -le $connections.Count; $i++)
{
    $connection = $connections[$i]
    while ($connection.ODBCConnection.Refreshing)
    {
        Start-Sleep -Seconds 3
    }
}

# Save
$wb.Save()

# Cleanup (quit, garbage collection)
$excel.Quit()
Remove-Variable -Name Excel
[GC]::Collect()
[GC]::WaitForPendingFinalizers()

This will launch Excel, open the document, refresh all connections, wait for the refresh to complete, save the file and close Excel.

You just need to pass the path to the file as %InputFilePath%. Since it's a static file, it should be simple enough.

Then, if you want it open, you can open it again via the Launch Excel action. Or, you can modify the PowerShell script to make it not close Excel at the end (delete the part that goes after $wb.Save() ). And then use Attach to running Excel in PAD to create an Excel instance variable that you can use later to close this Master File.

Launching the other file is simple. You can use Get files in folder with a name filter and then apply sorting to get the latest file. Alternatively, if, for example, you always run your flow on the date that is in the Sync File name (or the day after), you can calculate it by using Get current date and time then Add to date time (if needed to deduct 1 day for example) and then Convert text to date time to get it formatted as MMdd.

Then, finally, there is an action to Run Excel macro. So, when you have your files open, you can use this action to run your macros on the Sync file. Finally, use Save Excel and Close Excel to finish.

Make sure you close both files, so none of them stays open. You might want to initially keep the Excel instances visible, so you can see it and close it manually in case something breaks. But when you're done, you can definitely run the entire flow in the background with both instances being invisible. No need to use any UI at all.

In case something breaks and your file gets locked because it is open in another app, simply terminate the Excel process via the Task manager. This can happen if you launch an invisible instance of Excel and forget to close it.

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution.
If you like my response, please give it a Thumbs Up.

If you are interested in Power Automate, you might want to follow me on LinkedIn at https://www.linkedin.com/in/agnius-bartninkas/

-------------------------------------------------------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.
Regards, Agnius Bartninkas

View solution in original post

MichaelAnnis · ‎07-07-2023

Don’t see why not.

for the date, you just need to run through some logic.

If you run this, say on a Wednesday, do you want it to run for this Friday or last Friday?

the logic is to set a base Friday, Ex 06/30/2023.

now you’ll use that to calculate all Fridays after. So if I run any day after the Friday I need the file name, I say:

Subtract Dates (today - base Friday) = DayDifference

Day Difference divided by 7 gives Weeks (with decimal)

Truncate Weeks gives an integer of weeks from BaseFriday

Add Weeks(integer) to Base Friday to get the TargetFriday

Convert TargetFriday to text for the format you want ex. MM-dd

now you can add that text to your filename to make it dynamic.

hope this helps.

----
If my post has answered your question, please thumbs up and mark this post as a solution.

I also offer paid consulting services. If you would like to discuss this option, please feel free to DM me and we can set up a time to join a Zoom call and fix any issues you are having.

MichaelAnnis · ‎07-07-2023

Example:

DateTime.GetCurrentDateTime.Local DateTimeFormat: DateTime.DateTimeFormat.DateAndTime CurrentDateTime=> CurrentDateTime
/# we get the number of days from current date to our base monday
we divide that number by 7 to get the number of weeks (with a partial week as a decimal)
we remove the decimal to get the number of flat weeks
we add those weeks back to the base monday to get the monday for this week
we calc beg/end of period from the day before thismonday to a week before thismonday#/
DateTime.Subtract FromDate: CurrentDateTime SubstractDate: BaseMonday TimeUnit: DateTime.DifferenceTimeUnit.Days TimeDifference=> DayDifference
SET WeekDifference TO DayDifference / 7
Variables.TruncateNumber.GetIntegerPart Number: WeekDifference Result=> WeeksFromBase
DateTime.Add DateTime: BaseMonday TimeToAdd: WeeksFromBase * 7 TimeUnit: DateTime.TimeUnit.Days ResultedDate=> ThisMonday
DateTime.Add DateTime: ThisMonday TimeToAdd: -1 TimeUnit: DateTime.TimeUnit.Days ResultedDate=> EndOfPeriod
DateTime.Add DateTime: ThisMonday TimeToAdd: -7 TimeUnit: DateTime.TimeUnit.Days ResultedDate=> BegOfPeriod
# we put the beg/end of period into text format so we can populate clockshark
Text.ConvertDateTimeToText.FromDateTime DateTime: BegOfPeriod StandardFormat: Text.WellKnownDateTimeFormat.ShortDate Result=> BegOfPeriod_ShortDate
Text.ConvertDateTimeToText.FromDateTime DateTime: EndOfPeriod StandardFormat: Text.WellKnownDateTimeFormat.ShortDate Result=> EndOfPeriod_ShortDate
Text.ConvertDateTimeToText.FromCustomDateTime DateTime: EndOfPeriod CustomFormat: $'''MM.dd.yyyy''' Result=> EndOfPeriod_mmddyyyy_Dots

----
If my post has answered your question, please thumbs up and mark this post as a solution.

I also offer paid consulting services. If you would like to discuss this option, please feel free to DM me and we can set up a time to join a Zoom call and fix any issues you are having.

Agnius · ‎07-07-2023

Do not use the UI to refresh the connections in your file. It's unstable. Instead, use the following PowerShell script. We use it all the time for projects where we need to refresh the connections in a file:

# Excel input file
$xlsx = "%InputFilePath%"

# Create Excel COM object
$excel = New-Object -ComObject Excel.Application

# Make excel invisible
$excel.Visible = $false

# Disable excel alerts
$excel.DisplayAlerts = $false

# Open excel workbook
$wb = $excel.Workbooks.Open($xlsx)

# initiate refresh of the connections
$wb.RefreshAll()

# get all the connections in the work book
$connections = $wb.Connections

# for each connection wait while it is refreshing
for ($i = 1; $i -le $connections.Count; $i++)
{
    $connection = $connections[$i]
    while ($connection.ODBCConnection.Refreshing)
    {
        Start-Sleep -Seconds 3
    }
}

# Save
$wb.Save()

# Cleanup (quit, garbage collection)
$excel.Quit()
Remove-Variable -Name Excel
[GC]::Collect()
[GC]::WaitForPendingFinalizers()