Hi Everyone,
Hope you all keep well .
Can someone, please guide/help me for the below automation,
I have an excel file, which contains 23 sheets in the local system.
I need to copy each individual sheet’s data into 23 folders by creating a new excel file for each month and the folders are available in the network drive consisting of the main folder.
And also i want to check every month there is an excel file present for the current month.
I would really appreciate for your time and help.
@POOJA_MARUTI_BALIGIDE
actually i didn’t understand on you ,
but i think i can help you in some of it and you complete
there is an activity name path exist you should use it
after it you should add if statement if path not exist write range
else do nothing
all that inside the body in screenshot i sent
Hello Yazan,
I have an excel file, which contains 23 sheets in the local system, and i have paste the sheet wise data in to excel file inside each multiple folders.
I am using path exist activity to check the main folder (vendor folder).
2)if it is true , it has to paste the sheet wise data inside each Sub folders as shown in screenshot above reply.
3)Inside each sub-folders , i have to check if there is any existing current month excel file available.(filename :(*July 2023.xlsx).
4)if there is Current month excel available , sheet-vise data in to each subfolder.
else create new excel file in each subfolder and paste the sheet vise data respectively.
Multiple Sub-folders based on each Vendors ( AtoZ)
Each Sub folder contains Monthly Excel file ( new excel file to be created each month if not present)
)
After this, we have to paste the sheet vise data inside each subfolder of excel file ,
Please let me know if any more information required.
Loop Through Sheets and Create Excel Files
Use a “For Each” loop to iterate through each sheet in the local Excel file. Within the loop, create a new Excel file for each sheet’s data using the “Excel Application Scope” and the “Write Range” activity to write the data to the new file
Create Folders on the Network Drive
Using the “Create Directory” activity, create 23 folders on the network drive to store the Excel files for each month.
After creating the new Excel files for each sheet, use the “Copy File” activity to copy the created Excel files to their respective folders on the network drive.
To check if there’s an Excel file present for the current month, you can use the “Directory.GetFiles” method in an Assign activity to get a list of all files in the network drive’s main folder. Then, use the “Filter” method to filter the list and check if a file with the current month’s name exists.
You can optionally use the “Write Line” activity to display messages or use the “Write Cell” activity to store information about the files and their status in an Excel file.
I am using path exist activity to check the main folder (vendor folder).
2)if it is true , it has to paste the sheet wise data inside each Sub folders as shown in screenshot above reply.
3)Inside each sub-folders , i have to check if there is any existing current month excel file available.(filename :(*July 2023.xlsx).
4)if there is Current month excel available , sheet-vise data in to each subfolder.
else create new excel file in each subfolder and paste the sheet vise data respectively.
There is no need to created directory or folders .
the Directory and subfolders present in network path.
Multiple Sub-folders based on each Vendors ( AtoZ)
Each Sub folder contains Monthly Excel file ( new excel file to be created each month if not present)
)
After this, we have to paste the sheet vise data inside each subfolder of excel file ,
Please let me know if any more information required.
[/quote]
Read the Excel File: Use the “Excel Application Scope” activity to open the original Excel file that contains the 23 sheets.
Get the List of Subfolders: Use the “Directory.GetDirectories” method to get the list of subfolders inside the main vendor folder.
Loop Through Subfolders: Use a “For Each” loop to iterate through each subfolder.
Check If Monthly Excel File Exists: Inside the “For Each” loop, use the “Directory.GetFiles” method to check if the current month’s Excel file exists in the subfolder. If it exists, open the Excel file. If not, create a new Excel file with the current month’s name (e.g., “July 2023.xlsx”).
Loop Through Sheets: For each subfolder and corresponding Excel file, use another “For Each” loop to iterate through each sheet in the original Excel file.
Copy Sheet Data: Inside the nested “For Each” loop, use the “Copy Sheet” activity to copy the data from the current sheet in the original Excel file to the corresponding sheet in the subfolder’s Excel file.
Save and Close Excel Files: After copying all the sheet data, save and close the Excel files for each subfolder.
I am reading excel file to get all the sheets ,and using for each to loop through sheets.
i am using "for each folder in folder "activity to get all the sub-folders path.
(this output is printing all file names 23 times for each execution )
3)After this using "write range workbook "activity to write all the sheets data.but its not writing any file inside the folders.
Please find the attached excel. Main.xaml (12.5 KB)
@POOJA_MARUTI_BALIGIDE
Does the Datatable which u get in read range activity has contents , just check that in debug mode in debug panel
Also i tried your workflow and it works fine for me
Just check the filepath In for each folder activity, try giving the whole filepath
The data table in read range activity is null, I am not able to identify what is the issue…I have to read excel (multiple sheets) , and paste the data in each folder of main folder.
@lakshman ,
Can you please help me with you suggestion.
I have an excel file, which contains 23 sheets in the local system, and i have paste the sheet wise data in to excel file inside each multiple folders.
I am using path exist activity to check the main folder (vendor folder).
2)if it is true , it has to paste the sheet wise data inside each Sub folders as shown in screenshot above reply.
3)Inside each sub-folders , i have to check if there is any existing current month excel file available.(filename :(*July 2023.xlsx).
4)if there is Current month excel available , sheet-vise data in to each subfolder.
else create new excel file in each subfolder and paste the sheet vise data respectively.
Multiple Sub-folders based on each Vendors ( AtoZ)
Each Sub folder contains Monthly Excel file ( new excel file to be created each month if not present)
)
After this, we have to paste the sheet vise data inside each subfolder of excel file ,
[/quote]
@POOJA_MARUTI_BALIGIDE
May be u have problem with read range activity
Try removing and adding it back to the sequence or try excel activities
It might work
