1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to count the number of pages of multiple excel files (without opening each file) ?

Discussion in 'Ask an Excel Question' started by Amar Shafi, Sep 12, 2017.

  1. Amar Shafi

    Amar Shafi New Member

    As I have multiple Excel files within multiple different folders. I want to know how I can count the number of pages of each file within all the relevant folders without opening all the files individually. A I have over 300 Excel files that I need this done to.

    Ideally I don't want to use a Macro where possible. But if required I will.

    Thanks for your help & continued support
  2. Hui

    Hui Excel Ninja Staff Member


    Firstly, Welcome to the Chandoo.org Forums

    Do you mean Worksheets in each Workbook or Print Pages
  3. Amar Shafi

    Amar Shafi New Member

    Hey Hui, I mean Print Pages. Any suggestions?
  4. Hui

    Hui Excel Ninja Staff Member

    Evan a macro will have to loop through each file in the directory and open it to achieve what you want

    The Number of Worksheets or Print Pages is not shown in the Windows Explorer File Properties which was going to be my next suggestion of where to look
  5. Amar Shafi

    Amar Shafi New Member

    Hey Hui, even if the Macro needs to open up each file, then that's totally fine.

    What would the Macro Code be for this problem. Please note that I want the Print Pages Count for ALL the worksheets within a Workbook. But I only need one total for the workbook not for each individual worksheet.
  6. Hui

    Hui Excel Ninja Staff Member

    Run this file and see if it does what you want

    Attached Files:

    Syedali likes this.
  7. Amar Shafi

    Amar Shafi New Member

    Hey Hui, thanks for this, this is really awesome.

    1) However the Number of Print Pages is usually incorrect. As I need to have the "Print Entire Workbook" number of Print Pages to be displayed. Refer to the attached image for details.

    2) Also it currently only works with XLS files, but I need to run the Macro of CSV and XLSX file formats as well.

    Thanks for your continued support Hui, I really appreciate it.

    Attached Files:

  8. Hui

    Hui Excel Ninja Staff Member

    It processes 3 file types for me


    I'm not sure how accurate CSV Files will be as they contain no page settings and hence will default to the Local Default Printer and default font sizes and default margins etc, all which change the page layout

    Attached Files:

  9. Amar Shafi

    Amar Shafi New Member

    Thank you for this Hui, I really appreciate it, it does work.

    Your a Genius
  10. Hui

    Hui Excel Ninja Staff Member

    The issue with CSV Files is that they will default to the Default font and font size and page borders
    They could be included if you really wanted that
  11. Amar Shafi

    Amar Shafi New Member

    Hey Hui,

    I have been using the file for a few days without any problems. Once again, thanks for everything.

    But I have realized that once I run the Macro and it opens a "Read Only" file, the Macro doesn't work.

    Any suggestions on how to make it work with "Read Only" Files?

    If not then I am happy for it to skip that file and proceed to the next available file. But at the current moment it just stops and does not skip to the next available file.

    Your help is greatly appreciated.

    Any other suggestions are most welcomed.

    Best Regards,
  12. Marc L

    Marc L Excel Ninja


    Hi !

    Explain first why the file is Read Only ?
  13. Amar Shafi

    Amar Shafi New Member

    Hey Marc,

    Thanks for helping out.

    In fact don't worry about the Read Only files.

    I actually have another problem that I discovered.


    When a Workbook has multiple Worksheets what happens is that the Macro populates the Page Count for the first Worksheet times by the number of worksheets within the Workbook. So it does not actually calculate the total Page Count for the entire workbook.

    For example

    If a workbook has 5 worksheets and the page count on the first worksheet is 10 pages. The Macro will populate the page count as 50 pages (5 * 10) rather than counting the actual Page Count among all the pages.

    Your help is greatly appreciated.

    Thank you
  14. Hui

    Hui Excel Ninja Staff Member

    Can you please replace the code in the file with the code below

    Code (vb):

    Sub File_Deatils()

      Dim fldr As FileDialog
      Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
      With fldr
      .Title = "Select a Folder"
      .AllowMultiSelect = False
      .InitialFileName = Application.DefaultFilePath
      If .Show <> -1 Then GoTo 10
      sItem = .SelectedItems(1)
      End With

    10: path = sItem & "\*.xls*"

      cdir = path
      Sheet1.Name = "Excel File Details"
      Sheet1.Range("A1") = sItem
      Sheet1.Range("A3") = "No."
      Sheet1.Range("B3") = "File Name"
      Sheet1.Range("C3") = "No of Worksheets"
      Sheet1.Range("D3") = "No of Print Pages"
      Dim tgt As Worksheet, r As Long
      Set tgt = Sheet1
      r = 0
      Dim f As String
      f = Dir(path)
      Dim wb As Workbook, ws As Worksheet
      Dim iTotPages As Integer
      Do Until f = ""
      If f <> ThisWorkbook.Name Then
      Set wb = Workbooks.Open(sItem & "\" & f, ReadOnly:=True)
      r = r + 1
      iTotPages = 0
      For Each ws In wb.Sheets

      iTotPages = iTotPages + ActiveSheet.PageSetup.Pages.count
      tgt.Cells(r + 3, 1) = r
      tgt.Cells(r + 3, 2) = wb.Name
      tgt.Cells(r + 3, 3) = wb.Worksheets.count
      tgt.Cells(r + 3, 4) = iTotPages
      wb.Close savechanges:=False
      End If
      f = Dir()

    MsgBox r & " : files found in folder"

    End Sub
    If you still have troubles please post a sample file which causes errors
    Last edited: Sep 20, 2017
  15. Amar Shafi

    Amar Shafi New Member

    Hey Hui,

    Thanks for the above file.

    Now the Page Count for Workbooks with Multiple worksheets finally works. Happy Days.

    But now some of the Workbooks with a single worksheet are incorrect.

    If possible please arrange to resolve this problem regarding the wrong page count for some single page workbooks.

    Unfortunately I am unable to provide you with some sample files, this is due to the confidential nature of the files I am dealing with, sorry.

    Once again, thank you for your continued support Hui
  16. David Evans

    David Evans Active Member

    Sanitize them by replacing the proprietary information with junk. It's very hard for even Ninjas to fix problems that are described via a third party - it can be an awful waste of their effort going down ratholes ..
  17. Hui

    Hui Excel Ninja Staff Member


    can I please ask that you check your files as I cannot replicate the issue

    If you open the file
    Then click on the Page Break Preview at the lower right corner of Excel
    Then look for small slithers of Page 2 as either:

    A Vertical slice or

    A Horizontal slice

    My code works when there is only a single page:

Share This Page