Power automate create several files

(0) Share

Report

Posted on by awemoha

This is a exemple of a file

You the see the first column i have AA,AA,BB,BB

Now i want two files the first file i will have the first two lines : AA,AA and the second files BB,BB .

it's like filtering but after filtering we create a file containing only the filter.

Categories:

Power Automate Desktop

I have the same question (0)

All responses (16)

Answers (1)

Sort by

UshaJyothiKasibhotla 225 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @awemoha

Use run .net action and write the Linq

OUT_DT = IN_DT.Asenumerable.groupby(Function(a) a(0).tostring).select(function(b) b.copytodatatable).tolist

you will get list of datatables then loop through them and write into different files or different sheets

the images are attached below for reference

the code is attached below copy paste and check that it meets your requirements.

Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\Digital.Solutions\\Desktop\\PAD.xlsx''' Visible: True ReadOnly: False Instance=> ExcelInstance
Excel.SetActiveWorksheet.ActivateWorksheetByIndex Instance: ExcelInstance Index: 1
Excel.ReadFromExcel.ReadAllCells Instance: ExcelInstance ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelData
Scripting.RunDotNetScript Language: System.DotNetActionLanguageType.VisualBasic ReferenceRootPath: $'''C:\\PADAssembyReferences''' Script: $'''OUT_DT = IN_DT.Asenumerable.groupby(Function(a) a(0).tostring).select(function(b) b.copytodatatable).tolist''' @'name:IN_DT': ExcelData @'type:IN_DT': $'''Datatable''' @'direction:IN_DT': $'''In''' @'name:OUT_DT': $'''''' @'type:OUT_DT': $'''List''' @'direction:OUT_DT': $'''Out''' @OUT_DT=> OUT_DT
SET Count TO 2
LOOP FOREACH CurrentItem IN OUT_DT
Excel.SetActiveWorksheet.ActivateWorksheetByIndex Instance: ExcelInstance Index: Count
Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: CurrentItem Column: $'''A''' Row: 1
SET Count TO Count + 1
END

Hope this helps, if so please mark it as solution and kudo the post which keeps me motivated to solve.

Usha.

Was this reply helpful? Yes No
CU16071609-1 6,255 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

@UshaJyothi20 - Nice approach and welcome back to community 🎉

Was this reply helpful? Yes No
awemoha 35 on at

Like
a
(0)

Report
Copy link

Link copied!

thank you very much
but i use power automate not desktop i don't know if there is a difference

Was this reply helpful? Yes No
awemoha 35 on at

Like
a
(0)

Report
Copy link

Link copied!

but i use thyse type of power automate

Was this reply helpful? Yes No
UshaJyothiKasibhotla 225 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!

Thank you @Deenuji

And I observe your Pace of giving solutions.

Happy to learn together.

Usha.

Was this reply helpful? Yes No
CU16071609-1 6,255 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!

@awemoha

This forum is dedicated for desktop-based automations. Please raise your topic in another sub-forum where they will guide you to achieve your use case on cloud flows.
Building Flows - Power Platform Community (microsoft.com)

Thanks,
Deenuji Loganathan 👩‍💻
Automation Evangelist 🤖
Follow me on LinkedIn 👥

-------------------------------------------------------------------------------------------------------------
If I've helped solve your query, kindly mark my response as the solution ✔ and give it a thumbs up!👍 Your feedback supports future seekers 🚀

Was this reply helpful? Yes No
awemoha 35 on at

Like
a
(0)

Report
Copy link

Link copied!

sorry I didn't know but thank you very much for your help

Was this reply helpful? Yes No
UshaJyothiKasibhotla 225 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!

This is power automate cloud please raise a question in the link suggested by @Deenuji

Was this reply helpful? Yes No
UshaJyothiKasibhotla 225 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!

No worries @awemoha

Happy to help always.

Was this reply helpful? Yes No
VishnuReddy1997 2,666 Super User 2026 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @awemoha ,

If you want the flow in Power automate desktop.Please find the solution.

Code:
Scripting.RunVBScript.RunVBScript VBScriptCode: $'''Set objExcel = CreateObject(\"Excel.Application\") objExcel.Visible = True Set objWorkbook = objExcel.Workbooks.Open(\"C:\\Users\\OneDrive\\Desktop\\Power Automate Desktop\\Practice\\Excel\\input.xlsx\") Set objDictionary = CreateObject(\"Scripting.Dictionary\") \' Assuming data is in Sheet1 and starts from row 2 Set objWorksheet = objWorkbook.Sheets(\"Sheet1\") intRow = 2 Do Until objExcel.Cells(intRow, 1).Value = \"\" strKey = objExcel.Cells(intRow, 1).Value If objDictionary.Exists(strKey) Then objDictionary.Item(strKey) = objDictionary.Item(strKey) + 1 Else objDictionary.Add strKey, 1 End If intRow = intRow + 1 Loop \' Create new sheets for duplicates For Each strKey in objDictionary.Keys If objDictionary.Item(strKey) > 1 Then Set objNewSheet = objWorkbook.Sheets.Add objNewSheet.Name = strKey intRow = 2 intNewRow = 1 Do Until objWorksheet.Cells(intRow, 1).Value = \"\" If objWorksheet.Cells(intRow, 1).Value = strKey Then objWorksheet.Rows(intRow).Copy objNewSheet.Rows(intNewRow) intNewRow = intNewRow + 1 End If intRow = intRow + 1 Loop End If Next \' Save and Close Excel Workbook objWorkbook.Save objWorkbook.Close objExcel.Quit Set objWorksheet = Nothing Set objWorkbook = Nothing Set objExcel = Nothing ''' ScriptOutput=> VBScriptOutput7 Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\OneDrive\\Desktop\\Power Automate Desktop\\Practice\\Excel\\input.xlsx''' Visible: True ReadOnly: False Instance=> ExcelInstance Excel.GetAllWorksheets Instance: ExcelInstance Worksheets=> SheetNames LOOP FOREACH CurrentItem IN SheetNames IF CurrentItem <> $'''Sheet1''' THEN Excel.SetActiveWorksheet.ActivateWorksheetByName Instance: ExcelInstance Name: CurrentItem Excel.GetFirstFreeColumnRow Instance: ExcelInstance FirstFreeColumn=> FirstFreeColumn FirstFreeRow=> FirstFreeRow Excel.CopyCellsFromExcel.CopyCells Instance: ExcelInstance StartColumn: $'''A''' StartRow: 1 EndColumn: $'''A''' EndRow: FirstFreeRow - 1 Excel.LaunchExcel.LaunchUnderExistingProcess Visible: True Instance=> Output_ExcelInstance Excel.PasteCellsToExcel.PasteAt Instance: Output_ExcelInstance Column: $'''A''' Row: 1 Excel.CloseExcel.CloseAndSaveAs Instance: Output_ExcelInstance DocumentFormat: Excel.ExcelFormat.FromExtension DocumentPath: $'''C:\\Users\\\OneDrive\\Desktop\\Power Automate Desktop\\Practice\\Excel\\New folder\\%CurrentItem%''' END END

(Note:- if you got your solution you can mark as solution and gives kudos)

Thanks & Regards
Vishnu Reddy

Was this reply helpful? Yes No