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

[Amar Shafi]

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

 

[Hui]

Amar

Firstly, Welcome to the Chandoo.org Forums

Do you mean Worksheets in each Workbook or Print Pages

 

[Amar Shafi]

Amar

Firstly, Welcome to the Chandoo.org Forums

Do you mean Worksheets in each Workbook or Print Pages

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

 

[Hui]

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

 

[Amar Shafi]

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.

 

[Hui]

Run this file and see if it does what you want

 

[Amar Shafi]

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.

 

[Hui]

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

 

[Amar Shafi]

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

Your a Genius

 

[Hui]

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

 

[Amar Shafi]

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

 

[Marc L]

Hi !

Explain first why the file is Read Only ?
​

 

[Amar Shafi]

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

 

[Hui]

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

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

 

[Amar Shafi]

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

 

[David Evans]

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

 

[Hui]

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


A Horizontal slice


My code works when there is only a single page:

 

[danielbalko]

Litlle bit late...but thank you!
Wondering if we can use it on subfolders to...
Anyway great job!

 

[Marc L]

Anyway you can create your own thread in the appropriate VBA Macros forum section …​

​

 

[danielbalko]

Thanks Marc L... I'll do!
I am new...and keep reading....