Save Excel Document in specific Folder OneDrive

[Anonymous34]

Hi,

I am working on a flow where I want to save an Excel document in a specific folder. However, this folder changes depending on the project number. The folders are sorted by project number, and I have this number as a variable. I want the flow to find the folder corresponding to the project number, for example, "01267", and then save the document in the subfolder "ExceldocumentProject" within that folder.

In other words, based on the project number, the correct folder should be found. So if there is a folder with the number "01267", the subfolder within it should be selected.

Do you have any idea how this can be achieved?

[Ahammad_Riyaz]

Hi @Anonymous34 ,

You can try to use switch condition based on input number you can set.

If you have more numbers, then try to use like one config excel file and based on number get the appropriate folder name.

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,
Riyaz

[Anonymous34]

Hi, 

Thanks for your response!

The project number that keeps changing is user input. Therefore, I can't put it in an Excel file and iterate from there. The project number is manually entered by the user, which then initiates this flow and is stored in a variable.

The folders where I need to store an Excel document always start with the project number, and below that are a number of subfolders.

[Deenuji]

@Anonymous34 

You are trying to achieve this in desktop flow or clod flow?

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 🚀

[Anonymous34]

Hi @Deenuji,

In Desktop flow 🙂 

[NathanAlvares24]

Hi @Anonymous34 !

You can try this simple flow.

Initialize an input variable which you can trigger from outside and you just have to type the project number in that field.

Type your project number here to run:

So what this flow actually does is that, I launch an empty excel file (or maybe an already existing one but you can modify it accordingly), I do some processing for the excel file noted in the comments. Then I simply list out all the folders in that main directory. A list of folders will be produced. Iterate through each using for each loop. Check the condition if the name of the folder is containing the input variable value. If yes, get the subfolders within this project folder, I guess its always going to be "ExceldocumentProject". So you can index it to just the first folder given by index 0. Then save the excel file as:

Then close the excel as the "save document" and exit the loop because its doing for one excel file and once it got the folder, no need to keep iterating over and over.

Additionally, I added a boolean variable to keep check incase if you don't get the project folder. So if you get the folder, make it true and exit from the loop.

But if not found, i.e. it is still false, save the excel file as the same pic above and close the excel as the "save document".

My project folders and in each of them already there is a subfolder named "":

First round (incase if project folder doesn't exist):

Input:

Output:

Second Round (the case if project folder already and subfolder already exists and just save it there):

Input:

Output:

Third Round (the case if project folder already exists but its subfolder doesn't exist and just save it there):

I guess you got the point here as well.

Code (just copy-paste this code into your flow) :

SET MainFolderPath TO $'''C:\\Users\\Username\\Desktop\\ProjectFolders'''
SET boolFoundFolder TO False
Excel.LaunchExcel.LaunchUnderExistingProcess Visible: True Instance=> ExcelInstance
# ##########################################################################################
# Add the process part for excel here
# ##########################################################################################
Folder.GetSubfolders Folder: MainFolderPath FolderFilter: $'''*''' IncludeSubfolders: False FailOnAccessDenied: True SortBy1: Folder.SortBy.NoSort SortDescending1: False SortBy2: Folder.SortBy.NoSort SortDescending2: False SortBy3: Folder.SortBy.NoSort SortDescending3: False Subfolders=> Folders
LOOP FOREACH Folder IN Folders
    IF Contains(Folder.Name, varProjNumber, True) THEN
        Folder.GetSubfolders Folder: Folder FolderFilter: $'''*''' IncludeSubfolders: False FailOnAccessDenied: True SortBy1: Folder.SortBy.NoSort SortDescending1: False SortBy2: Folder.SortBy.NoSort SortDescending2: False SortBy3: Folder.SortBy.NoSort SortDescending3: False Subfolders=> SubFolders
        IF IsEmpty(SubFolders) THEN
            Folder.Create FolderPath: Folder FolderName: $'''ExceldocumentProject''' Folder=> NewSubFolder
            Excel.SaveExcel.SaveAs Instance: ExcelInstance DocumentFormat: Excel.ExcelFormat.FromExtension DocumentPath: $'''%NewSubFolder%\\%varProjNumber%'''
        ELSE
            Excel.SaveExcel.SaveAs Instance: ExcelInstance DocumentFormat: Excel.ExcelFormat.FromExtension DocumentPath: $'''%SubFolders[0]%\\%varProjNumber%'''
        END
        Excel.CloseExcel.CloseAndSave Instance: ExcelInstance
        SET boolFoundFolder TO True
        EXIT LOOP
    ELSE
        NEXT LOOP
    END
END
IF boolFoundFolder = False THEN
    Folder.Create FolderPath: MainFolderPath FolderName: $'''%varProjNumber%_ProjectFiles''' Folder=> NewFolder
    Folder.Create FolderPath: NewFolder FolderName: $'''ExceldocumentProject''' Folder=> NewSubFolder
    Excel.SaveExcel.SaveAs Instance: ExcelInstance DocumentFormat: Excel.ExcelFormat.FromExtension DocumentPath: $'''%NewSubFolder%\\%varProjNumber%'''
    Excel.CloseExcel.CloseAndSave Instance: ExcelInstance
END

I hope this helps.

[Anonymous34]

Hi @NathanAlvares24,

Thanks for your response! It works somewhat and also saves it but not where I want it. Where I eventually want it to save it is in the a subfolder of a subfolder. I will explain with screenshots.

I have here below the Projectnumber

Then i want the open the folder that contains 02698 ( this works 🙂 )

Then i want in these map to open the folder that contains "Verslagen" (see screenshot)

And in these folder i want to save the excel document in "01 Bouw(team)vergadering"

Thanks in advance for your help!

[Agnius]

If you're syncing the OneDrive directory to your machine via the OneDrive app, you can use Get subfolders in folder action to get the subfolders and use the project number in the filter. This will return the one folder for your project. Then you just use the first item in the returned list as a part of the path, and specify any remaining folders inside it additionally in the Save Excel action, or Move file(s) action, if you don't have the Excel document open.

The loop that @NathanAlvares24 is absolutely unnecessary, because what they did was simply put a * in the folder filter in Get subfolders in folder, when you could have really just used %varProjectNumber%*  as the filter there to only return the one folder you need.

[Anonymous34]

Hi @Agnius,

Thank you for your response. 🙂

What exactly do you mean? I did the activity Get subfolders in folder with the filter of Project Number. But what are the actions after that, because how do I finally get into the subfolders and then save the excel document?

Thank you!

[Agnius · Accepted solution]

%Folders% is a list of folders. %Folders[0]% will return the first item, and %Folders[0].FullName% is the full path to it. But that's just the project folder, so you need to add the remaining bits to it in the Save Excel action. Assuming they're static, you can use this:

%Folders[0].FullName%\008 - Verslagen\01 Bouw(team)vergadering\{somefilename}.xlsx

[Anonymous34]

Hi @Agnius,

It works! Thank you so much. I just couldn't figure it out! Thank you!! 😁