How do I change this vba to search for pdfs folders that might or might not have sub-folders.

Question

0.00/5 (No votes)

See more:

Sorry I am new to programming and going to school for it. I found this macro for excel a while back and tried getting some help on it. No replies. I tried to code it with other macros I have and no good outcomes.

What I want this macro to do is go through a folder that might or might not have sub-folders.

VB

Sub PDFPageNumbers()
    Dim FSO As Object
    Dim F_Folder As Object
    Dim F_File As Object
    Dim Selected_Items As String
    Dim DialogFolder As FileDialog
    Dim Acrobat_File As Acrobat.AcroPDDoc
    Dim i As Long

    'Select PDF Directory
    Set DialogFolder = Application.FileDialog(msoFileDialogFolderPicker)
    If DialogFolder.Show = -1 Then
        Selected_Items = DialogFolder.SelectedItems(1)
    Else: Set DialogFolder = Nothing
    End If
    Set DialogFolder = Nothing
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set F_Folder = FSO.GetFolder(Selected_Items)
    i = 2
    For Each F_File In F_Folder.Files
        Selected_Items = UCase(F_File.Path)
        If Right(Selected_Items, 4) = ".PDF" Then
            Set Acrobat_File = New Acrobat.AcroPDDoc
            Acrobat_File.Open Selected_Items
            Cells(i, 1).Value = Selected_Items
            Cells(i, 2).Value = Acrobat_File.GetNumPages
            i = i + 1
            Acrobat_File.Close
            Set Acrobat_File = Nothing
        End If
    Next

    Range("A:B").Columns.AutoFit

    Set F_File = Nothing
    Set F_Folder = Nothing
    Set FSO = Nothing

End Sub

Posted 8-Jan-14 10:25am

Dark91zc

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Pete O'Hanlon · Answer 1 · 2014-01-08T10:34:00

In order to go through the sub folders, you first have to find them. Your F_Folder variable contains the current folder, and this gives you access to a SubFolders property. Use this to loop through the sub folders in the same manner (note that you will have to perform this operation across each sub folder as well). To ease things for yourself, you should split the functionality out for iterating over the files into a separate routine and call that from your folder code. Effectively you would end up with something like this:

VB

    Set F_Folder = FSO.GetFolder(Selected_Items)
    IterateFolders(F_Folder, i)
.....

Sub IterateFolders(ByVal F_Folder, ByRef i)
    For Each SubFolder In F_Folder
        IterateFolders(SubFolder, i)
    Next
    For Each F_File In F_Folder.Files
        Selected_Items = UCase(F_File.Path)
        If Right(Selected_Items, 4) = ".PDF" Then
            Set Acrobat_File = New Acrobat.AcroPDDoc
            Acrobat_File.Open Selected_Items
            Cells(i, 1).Value = Selected_Items
            Cells(i, 2).Value = Acrobat_File.GetNumPages
            i = i + 1
            Acrobat_File.Close
            Set Acrobat_File = Nothing
        End If
    Next
End Sub