Power Platform Community Forum Thread Details

Hello everyone,

I want to write the header of the Excel document. By document name but only the first 7 letters

an example;

file name: 'ABD-101 position 1 testing' ⇾ header page: ABD-101

file name: 'ABD-200 position 2 retesting' ⇾ header page: ABD-200

I have no idea how to use power automate.

I open to all solution, not only power automate or script in Excel.

Thank you for advance

Categories:

Power Automate Desktop

Based on the limited information provided, I would suggest something like this:

Get files in folder - this will return the files you need to process in a list of files stored in a variable called %Files%
For each - this will build a loop to iterate through each file in %File%, storing the current file in %CurrentItem%
1. Get subtext - this will get you the first 7 characters of the file name. Use %CurrentItem.NameWithoutExtension% to get the file name.
2. Launch Excel - pretty self-explanatory. Use %CurrentItem% as the file path.
3. Write to Excel worksheet - use this to write the output of Get subtext into the cell you want.
4. Save Excel
5. Close Excel

This will essentially process all Excel files in the given directory. If you want to skip some files, you may need to have an If statement inside the loop with some condition for skipping a file.

Here's what the flow should look like:

And here's a code snippet you can copy and paste directly into your PAD designer to get all those actions:

Folder.GetFiles Folder: $'''C:\\RPA''' FileFilter: $'''*.xls*''' IncludeSubfolders: False FailOnAccessDenied: True SortBy1: Folder.SortBy.NoSort SortDescending1: False SortBy2: Folder.SortBy.NoSort SortDescending2: False SortBy3: Folder.SortBy.NoSort SortDescending3: False Files=> Files
LOOP FOREACH CurrentItem IN Files
 Text.GetSubtext.GetSubtext Text: CurrentItem.NameWithoutExtension CharacterPosition: 0 NumberOfChars: 7 Subtext=> Subtext
 Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: CurrentItem Visible: True ReadOnly: False Instance=> ExcelInstance
 Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: Subtext Column: $'''A''' Row: 1
 Excel.SaveExcel.Save Instance: ExcelInstance
 Excel.CloseExcel.Close Instance: ExcelInstance
END

Please note you will likely need to change the directory in the Get files in folder action, as well as the column and row in the Write to Excel worksheet action.

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

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.