• 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...

  • 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) ?

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
 
Amar

Firstly, Welcome to the Chandoo.org Forums

Do you mean Worksheets in each Workbook or Print Pages
 
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
 
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
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.
 
Run this file and see if it does what you want
 

Attachments

  • Workbook Details.xlsm
    18.7 KB · Views: 17
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.
 

Attachments

  • Image.png
    Image.png
    36.2 KB · Views: 9
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
 

Attachments

  • Workbook Details.xlsm
    19.1 KB · Views: 15
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
 
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
 
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
 
Can you please replace the code in the file with the code below

Code:
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:
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
 
Hey Hui,

<snip>

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

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 ..
 
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
 
Anyway you can create your own thread in the appropriate VBA Macros forum section …​
 
Back
Top