Hi,
I am trying to create a list of all the files in a particular folder. I am aware one way i can get is using Data -> Get Data -> From File -> From folder option. This works perfectly for me. The only additional requirement i have is i need no of pages in each file in the excel list as well. Can anyone suggest me a way for that.
My folder has multiple files such as ppt, word, pdf, excel. I need no of pages for word and excel file against each row, ppt and excel is not necessary as such.
Regards
Shreeram
Get a list of files in a respective folder
-
- 2StarLounger
- Posts: 181
- Joined: 19 Feb 2016, 16:54
- Location: Veraval, India
-
- 2StarLounger
- Posts: 181
- Joined: 19 Feb 2016, 16:54
- Location: Veraval, India
Re: Get a list of files in a respective folder
Just a small correction. I need no of pages of word and pdf files, rest all is ok.
-
- Administrator
- Posts: 12577
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Get a list of files in a respective folder
The number of pages in a Word file depends on the default printer that you have configured, it is not an attribute of the file.
StuartR
-
- 2StarLounger
- Posts: 181
- Joined: 19 Feb 2016, 16:54
- Location: Veraval, India
Re: Get a list of files in a respective folder
it means there isn't any way or trick i can get it other than manually noting it down ?
-
- Administrator
- Posts: 78235
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Get a list of files in a respective folder
I don't know how to get the number of pages in a .pdf file. Here is a macro that lists all files, and the number of pages in Word documents:
Code: Select all
Sub ListFiles()
Dim strPath As String
Dim strFile As String
Dim w As Worksheet
Dim r As Long
Dim f As Boolean
Dim p As Long
Dim objWrd As Object
Dim objDoc As Object
With Application.FileDialog(4) ' msoFileDialogFilePicker
If .Show Then
strPath = .SelectedItems(1)
Else
Beep
Exit Sub
End If
End With
On Error Resume Next
Set objWrd = GetObject(Class:="Word.Application")
If objWrd Is Nothing Then
Set objWrd = CreateObject(Class:="Word.Application")
f = True
End If
On Error GoTo ErrHandler
Application.ScreenUpdating = False
Set w = Worksheets.Add
w.Range("A1").Value = "Files in " & strPath
w.Range("A2").Value = "File name"
w.Range("B2").Value = "Number of pages"
If Right(strPath, 1) <> Application.PathSeparator Then
strPath = strPath & Application.PathSeparator
End If
r = 3
strFile = Dir(strPath & "*.*")
Do While strFile <> ""
w.Range("A" & r).Value = strFile
p = InStrRev(strFile, ".")
Select Case LCase(Mid(strFile, p + 1))
Case "doc", "docx", "docm"
Set objDoc = objWrd.Documents.Open(Filename:=strPath & strFile, ReadOnly:=True, AddToRecentFiles:=False)
w.Range("B" & r).Value = objDoc.ActiveWindow.Panes(1).Pages.Count
objDoc.Close SaveChanges:=False
End Select
r = r + 1
strFile = Dir
Loop
ExitHandler:
On Error Resume Next
w.Range("A1:B1").EntireColumn.AutoFit
Application.ScreenUpdating = True
If f Then
objWrd.Quit SaveChanges:=False
End If
Exit Sub
ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 181
- Joined: 19 Feb 2016, 16:54
- Location: Veraval, India
Re: Get a list of files in a respective folder
Thanks Hans