You can do it dynamically, but then you should in fact not use a Switch statement. As you have correctly noted, a Switch will only work when you have a predefined set of values. If you want it to work dynamically, you should do it differently.
Here's an approach I would use:
This will use the vendor name as the file name. It will then check if the file exists already. If it does, it will open the existing file. If it doesn't, it will open a blank document and then save it as the file path. This way, you will be checking for a file with the vendor name on every row, but if you have already created a file like that, you will not be creating a new one.
Also, it uses the Get first free column/row from Excel worksheet to figure out which row to write to, as that will be different per file.
Here's a snippet you can copy and paste to PAD:
SET Directory TO $'''C:\\RPA'''
LOOP FOREACH CurrentItem IN ExcelData
SET FilePath TO $'''%Directory%\\%CurrentItem['Vendor']%.xlsx'''
IF (File.IfFile.Exists File: FilePath) THEN
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: FilePath Visible: True ReadOnly: False Instance=> ExcelInstance2
ELSE
Excel.LaunchExcel.LaunchUnderExistingProcess Visible: True Instance=> ExcelInstance2
END
Excel.GetFirstFreeColumnRow Instance: ExcelInstance2 FirstFreeColumn=> FirstFreeColumn FirstFreeRow=> FirstFreeRow
Excel.WriteToExcel.WriteCell Instance: ExcelInstance2 Value: CurrentItem['Vendor'] Column: $'''A''' Row: FirstFreeRow
Excel.WriteToExcel.WriteCell Instance: ExcelInstance2 Value: CurrentItem['Amount'] Column: $'''B''' Row: FirstFreeRow
Excel.SaveExcel.SaveAs Instance: ExcelInstance2 DocumentFormat: Excel.ExcelFormat.FromExtension DocumentPath: FilePath
Excel.CloseExcel.Close Instance: ExcelInstance2
END
Just make sure you change the value of the %Directory% variable to the actual directory where you want to save your file.
-------------------------------------------------------------------------
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.
