How to build a desktop flow using power automate for excel

[Hana123TEI]

I am new to Power automate and need assistance to for excel data .

My data is saved in folder and the files name is dynamic Mostly at the end month is added

e.g. Categoryplan202109 and  Classificationplan202110

this files will be there in folder , I have to always copy some columns data to other template .

but the challenge is I have to make it in such way that  when it is Categoryplan202109  it should paste the data in the template 

sheet Last Month and when the file is Classificationplan202110 it should paste the data in template current Month.

Also Categoryplan and Classificationplan name is fixed but data changes how I can achieve this using power automate.

Appreciate if someone help me  on this , and thank you in advance.

Regards,

**bleep**al.

[PetrosF-MSFT]

Hi @Hana123TEI,
At first, you could use the "Date time" actions to retrieve the current and previous months.
Then, use the "Get files in folder" action to retrieve the list of files.
Loop through the list of files and retrieve their names using the "Get file path part" action.
Extract the last part of the name using the "Parse text" action.
Compare the last part of the name (eg. 202109) with the DateTime variable.

[VJR]

Hi @Hana123TEI 

For looping through files use "Get Files in Folder" action.

Copy paste the below code in a new PAD editor to test its output.

IF Contains(InputName, $'''Categoryplan''', True) THEN
    Text.Replace Text: InputName TextToFind: $'''Categoryplan''' IsRegEx: False IgnoreCase: True ReplaceWith: $'''%''%''' ActivateEscapeSequences: False Result=> Replaced
    Text.ToDateTimeCustomFormat Text: Replaced CustomFormat: $'''yyyyMM''' DateTime=> TextAsDateTime
    Text.FromCustomDateTime DateTime: TextAsDateTime CustomFormat: $'''MM''' Result=> OutputMonth
ELSE IF Contains(InputName, $'''Classificationplan''', True) THEN
    Text.Replace Text: InputName TextToFind: $'''Classificationplan''' IsRegEx: False IgnoreCase: True ReplaceWith: $'''%''%''' ActivateEscapeSequences: False Result=> Replaced
    Text.ToDateTimeCustomFormat Text: Replaced CustomFormat: $'''yyyyMM''' DateTime=> TextAsDateTime
    Text.FromCustomDateTime DateTime: TextAsDateTime CustomFormat: $'''MM''' Result=> OutputMonth
END
Display.ShowMessage Message: OutputMonth Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed

The OutputMonth at the end of this code will give you the month number.

To run the code make sure to add the Input variable as marked below.

[MichaelAnnis]

For any repetitive activities you consistently do in Excel, you will want to write this in Excel VBA. 

Once you have the Excel VBA macro, PAD is very good at “Launch Excel” “Run Excel Macro” and then doing anything after.