I found this code, it works great, however, my need requires 1 change:
1) Change the static path to a dynamic "browse to location"
Sub CountPagesInDocs()
Const wdStatisticPages = 2
Dim wsStats As Worksheet
Dim objWrd As Object
Dim objDoc As Object
Dim strFileName As String
Dim strPath As String
Dim arrStats()
Dim cnt As Long
strPath = "C:Test" ' change this to the folder/path where you are storing your Word documents
strFileName = Dir(strPath & "*.doc*")
Set objWrd = CreateObject("Word.Application")
objWrd.Visible = False
Do While Len(strFileName) <> 0
ReDim Preserve arrStats(1 To 2, cnt)
Set objDoc = objWrd.Documents.Open(strPath & strFileName)
arrStats(1, cnt) = strFileName
arrStats(2, cnt) = objDoc.ComputeStatistics(wdStatisticPages)
objDoc.Close
cnt = cnt + 1
strFileName = Dir
Loop
objWrd.Quit
Set objWrd = Nothing
Set wsStats = Sheets.Add
With wsStats
.Range("A1:B1").Value = Array("Document Name", "No of Pages")
.Range("A2:B2").Resize(UBound(arrStats, 2) + 1).Value = Application.Transpose(arrStats)
.Range("A1:B1").EntireColumn.AutoFit
End With
End Sub
Add this function below your code:
Function GetFolder() As String
Dim dlg As FileDialog
Set dlg = Application.FileDialog(msoFileDialogFolderPicker)
dlg.InitialFileName = "C:"
If dlg.Show = -1 Then
GetFolder = dlg.SelectedItems(1)
End If
End Function
then amend your line to:
strPath = GetFolder & ""
@Velouria,
That was an AMAZING answer!!! I am new to functions, and most of the time, I have seen others provide a "browse code" to replace the path with. However your answer was so completely simple! I can EASILY use that code line and function code in other codes of mine to provide the browse for folder (I hate hard-coded folders). I am so excited!!! Thanks so very much!!!!!
You're welcome. 🙂
That's exactly why I have it as a function - reusable anywhere!
