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.

    Yours,
    Chandoo
  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

    Hui...

  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

    Messages:
    8
    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

    Messages:
    10,772
    Amar

    Firstly, Welcome to the Chandoo.org Forums

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

    Amar Shafi New Member

    Messages:
    8
    Hey Hui, I mean Print Pages. Any suggestions?
    Thanks
  4. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,772
    No
    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

    Messages:
    8
    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

    Messages:
    10,772
    Run this file and see if it does what you want

    Attached Files:

    Syedali likes this.
  7. Amar Shafi

    Amar Shafi New Member

    Messages:
    8
    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

    Messages:
    10,772
    It processes 3 file types for me

    upload_2017-9-13_23-0-16.png

    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

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

    Your a Genius
  10. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,772
    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

    Messages:
    8
    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,
    Amar
  12. Marc L

    Marc L Excel Ninja

    Messages:
    3,225

    Hi !

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

    Amar Shafi New Member

    Messages:
    8
    Hey Marc,

    Thanks for helping out.

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

    I actually have another problem that I discovered.

    PROBLEM:

    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

    Messages:
    10,772
    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*"
      Cells.ClearContents

      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

      ws.Activate
      iTotPages = iTotPages + ActiveSheet.PageSetup.Pages.count
     
      Next
     
      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()
      Loop

    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

    Messages:
    8
    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

    Messages:
    647
    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

    Messages:
    10,772
    Amar

    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
    upload_2017-9-29_14-50-58.png

    A Horizontal slice
    upload_2017-9-29_14-53-10.png

    My code works when there is only a single page:
    upload_2017-9-29_14-54-25.png

Share This Page