Get a list of files in a respective folder

shreeram.maroo
2StarLounger
Posts: 181
Joined: 19 Feb 2016, 16:54
Location: Veraval, India

Get a list of files in a respective folder

Post by shreeram.maroo »

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

shreeram.maroo
2StarLounger
Posts: 181
Joined: 19 Feb 2016, 16:54
Location: Veraval, India

Re: Get a list of files in a respective folder

Post by shreeram.maroo »

Just a small correction. I need no of pages of word and pdf files, rest all is ok.

User avatar
StuartR
Administrator
Posts: 12577
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: Get a list of files in a respective folder

Post by StuartR »

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


shreeram.maroo
2StarLounger
Posts: 181
Joined: 19 Feb 2016, 16:54
Location: Veraval, India

Re: Get a list of files in a respective folder

Post by shreeram.maroo »

it means there isn't any way or trick i can get it other than manually noting it down ?

User avatar
HansV
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

Post by HansV »

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

shreeram.maroo
2StarLounger
Posts: 181
Joined: 19 Feb 2016, 16:54
Location: Veraval, India

Re: Get a list of files in a respective folder

Post by shreeram.maroo »

Thanks Hans