If there are multiple sheet in an excel file ,how to iterate each file in UIPath

Help
,
1

Hi ,
I have an excel file with multiple sheet.Using UIPath flow diagram ,how can I iterate each sheet.

Thanks.

4 Likes
2

@Dhanushree Use sheets={“sheet1”,“sheet2”} and assign the excel path to other variable.Place for each and iterate through each sheet and use readrange

2 Likes
3

Hi,
When you use Excel Application Scope (EAS) activity you can get the sheet names into an array of string using GetSheets method on WorkbookApplication variable. Also the EAS creates a local variable of that type called “ExcelWorkbookScope” so you can use it for that and don’t bother creating new. Later is just a for each loop. Example attached.
HowToIterateThroughExcelSheets.xaml (8.4 KB)

4 Likes
4

@tomek.pawlicki Thanks.
sheets are getting iterated one by one. Now how could I save each sheet in PDF format?

1 Like
5

Hello @Dhanushree,

  1. Go to File menu=> Save As then select save as type PDF as below picture,

Excel%20to%20PDF
Excel%20to%20PDF713Ă—573 53.1 KB

Thanks,
Pankaj

1 Like
6

Every loop you can use Invoke Method activity on ExcelWorkbookScope and in the MethodName use SetSheet method, which takes two arguments sheetName as String and CreateNew as Boolean,… so in this scenario first is our sheet and second is always false = as we don’t want to create any new sheets (or we do?).
This way workflow will set active sheet which will be saved to PDF using the approach presented by @Pankaj.Patil

image

1 Like
7

@Pankaj.Patil, This will save all the sheets within one pdf. But I want to save each sheet as separate PDF. How can I achieve this?

8

Hello @Dhanushree,

Click on button Options then Options dialog will come up.
there is option(in read square) called “Active Sheet(s)”. it will save each sheet as separate PDF file.
have a look at pic below,

Save%20PDF
Save%20PDF620Ă—545 74.3 KB

Thanks,
Pankaj

9

Hi @Pankaj.Patil,
I already tried with this method.But the problem I’m facing is,it only consider the present sheet and that will be converted to PDF.If there are 10 sheets ,at a time I want to save all those sheet to 10 separate PDF.How can I do that?

Thanks.

1 Like
10

Hi @tomek.pawlicki,
I’m new to UIpath.So I’m not sure how to use invoke method.By using your solution for HowToIterateThroughExcelSheets, I’m able to iterate through sheets.But in a single stretch it loops and stops at the last sheet. I want to iterate one sheet,save to PDF,iterate second sheet,save to PDF,so on .Can you help me in this?
Thanks.

1 Like
11

Hello @Dhanushree,

Fantastic… you get each sheet,
put below code within for each activity,
Please read annotation that shows activity task.

Main.xaml (11.1 KB)

you must uncheck an options called “Open file after publishing”. converted(PDF) file will not open up
after you click button save.
Publish%20PDF

Thanks,
Pankaj

1 Like
12

Hi @Pankaj.Patil,
Using hotkey require manual interaction right.

1 Like
13

Hello @Dhanushree,

I’m sorry, I’ve not understood what you’re telling

1 Like
14

Hi @Pankaj.Patil ,
Sorry for miscommunication.
Can you say how these hot key works?
Thanks.

1 Like
15

Hi @Dhanushree

You can use below steps to convert each excel sheet to pdf.

  1. Get the sheets name using “GetSheets” method of WorkbookApplication.
  2. Use “excel application scope” activity to open the excel.
  3. send a hotkey “ctrl+g” to the excel file.
  4. Type “Sheetname!A1” to open the sheet.- Sheetname ->you will get after looping in the object(String) which you get from point 1.
  5. Go to File menu=> Save As then select save as type PDF
16

Hi @Priya_Dubey,
I’m not able to find how to add Type.Below I have attached my solution.Can you go through it and can you say what changes I have to do.
IterateThroughSheet.xaml (9.8 KB)

Thank you.

17

@Dhanushree I have created a sample workflow for your reference. Please see the attached file.ConvertExcelSheetToPDF.zip (182.4 KB)

1 Like
18

Hi @Priya_Dubey ,

From your solution I got a knowledge to deal with multiple sheets.Thank you for your time.In my solution PDF file is opening in internet explorer,and for one sheet I’m able to close the PDF and after 2nd iteration,this loop stops.How can I deal with this problem?
Thank you.

19

@Dhanushree You might need to change the selector for “Close Application” activity. Please check if the selector doesn’t contain any hardcoded value (file name, number etc). The workflow that i shared, contains the Adobe acrobat reader but as you mentioned your PDF opens up in a browser. So, instead of Close Application you will have to use “Close tab” activity.

20

@Priya_Dubey, while using close tab ,it doesn’t ask about url.How does Uipath get to know which tab to close?

close

Thank you.