none
Find the title of a pdf document RRS feed

  • Question

  • Hi all,

    Can you help me with a vba code such that i can find the title of a document if i specify the path of the file.

    In some of the pdf files if u view the properties of it, you can get the title of it.So is there any vba code to get it.

    I guess we can use the library files to get but can you help me with that........

    Plzzz help me............


    Veeru

    Saturday, March 17, 2012 5:31 PM

Answers

  • Hello again Veeru,

    I have tried and have not been successful in retrieving the Title that matches the Title that I see if I open the pdf file with Adobe Reader and select File -> Properties.

    In my code the Title always comes up empty even though I can see it from Properties in Adobe Reader. Anyway because you said that you have my previously posted code working, I have written some code that you might like to try. I am not familiar with VBA in Word. Therefore I have used Excel and the output is to a new Excel workbook. Your options are to copy the output and paste into a Word document (it becomes a table in Word) or start a new thread in this forum and post the code and ask if someone can translate it from Excel to a Word.

    If the property for the required property name is not found then in the output you will see something like the following: "Title" has no property or is an invalid property name.

    The code between the asterisk lines gives you an option of having the property name hard coded or entering a property name via an InputBox. See my comments in that area of code.

    Run the code from Sub GetFolder()

    Note my following commment re the reference for Shell.

    'Select VBA Menu Item Tools -> References and check the box _
     against Microsoft Shell Controls and Automation (Ensure you _
     check the box; not just highlight the line)

    Sub GetFolder()
        Dim fileDialog As fileDialog
        Dim strFileName As String
        Dim strPath As String
        Dim dialogTitle As String
        Dim wbNew As Workbook
        Dim lngRow As Long
        Dim strPropName As String
        Dim strProp As String
       
        dialogTitle = "Navigate to and select required folder."
       
        Set fileDialog = Application.fileDialog(msoFileDialogFolderPicker)
       
        With fileDialog
            .InitialFileName = "C:\"
            .AllowMultiSelect = False
            .Filters.Clear
            .Title = dialogTitle
            If .Show = False Then
                MsgBox "User cancelled without selecting a folder." & vbCrLf & _
                        "Processing terminated."
                Exit Sub
            End If
             
            strPath = .SelectedItems(1)
            'Some conditions exist where the backslash will already exist _
             with the selection. (Example: If initial path selected.)
            If Right(strPath, 1) <> "\" Then strPath = strPath & "\"
           
            '***************************************************************************************
            strPropName = "Title"
           
            'Alternative code to use an Inputbox in lieu of hard coding property name.
            'Comment out the line strPropName = "Title" above and uncomment the next line.
            'strPropName = Application.InputBox(Prompt:="Enter the name of the required property.", _
                            Title:="Get property name", Type:=2)
            '***************************************************************************************
           
            Set wbNew = Workbooks.Add
            wbNew.Sheets(1).Cells(1, 1) = "File Name"
            wbNew.Sheets(1).Cells(1, 2) = strPropName
            strFileName = Dir(strPath & "*.pdf")
            lngRow = 1
            Do While strFileName <> ""
                lngRow = lngRow + 1
                wbNew.Sheets(1).Cells(lngRow, 1) = strFileName
                strProp = GetProperty(strPath, strFileName, strPropName)
                wbNew.Sheets(1).Cells(lngRow, 2) = strProp
                strFileName = Dir
            Loop
              
        End With
       
        Windows(wbNew.Name).Activate
        Sheets(1).Activate
        Range("A1:B1").Font.Bold = True
        Columns("A:B").Columns.AutoFit
        Set wbNew = Nothing
        Set fileDialog = Nothing
     
    End Sub


    Function GetProperty(fileFolder As String, fileName As String, strName As String)
        'All properties are saved in an array so that the actual name of the _
         property can be found in lieu of using the numbered index of the property.
        Dim objShell As Shell32.Shell
        Dim objFolder As Shell32.Folder
        Dim objItem As Shell32.FolderItem
        Dim ws As Worksheet
        Dim r As Long
        Dim j As Long
        Dim varTemp As Variant
        Dim strTemp As String
        Dim arrProperties()
       
        Set objShell = New Shell
        Set objFolder = objShell.Namespace(fileFolder)
        Set objItem = objFolder.ParseName(fileName)
       
        With objFolder
            For r = 1 To 1000
                strTemp = .GetDetailsOf(objItem.Name, r)
                If strTemp = "" Then Exit For   'At end of properties
                varTemp = .GetDetailsOf(objItem, r)
                If varTemp <> "" Then   'Ignores properties with no value
                    j = j + 1
                    ReDim Preserve arrProperties(1 To 2, 1 To j)
                    arrProperties(1, j) = strTemp
                    arrProperties(2, j) = varTemp
                End If
            Next r
        End With
       
        Set objItem = Nothing
        Set objFolder = Nothing
        Set objShell = Nothing

        For j = LBound(arrProperties, 2) To UBound(arrProperties, 2)
            If UCase(arrProperties(1, j)) = UCase(strName) Then     'Ignore case for search
                GetProperty = arrProperties(2, j)
                Exit For
            End If
        Next j
        If j > UBound(arrProperties, 2) Then
            GetProperty = Chr(34) & strName & Chr(34) & _
                        " has no property or is an invalid property name."
        End If
       
    End Function


    Regards, OssieMac

    • Marked as answer by Veerusat Wednesday, March 21, 2012 9:10 AM
    Wednesday, March 21, 2012 3:23 AM
  • Hello Veeru,

    Use Developer Ribbon -> Insert ActiveX TextBox to place a text box on the first sheet of the excel workbook with the VBA code. The TextBox must be TextBox1. Or edit the code to change TextBox1 to the name of your TextBox. (2 places; one in each sub. See comments.)

    Run Sub GetFolder() to Browse for the required folder.

    Run Sub ProcessFiles() to get the Files and Properties.

    Assuming that you have inserted the code into a standard module then use the Form control buttons and assign the required macros to them. (GetFolder and ProcessFiles.)

    Keep the Function GetProperty(......) code from my previous post because it is still required in addition to the following code.

    Sub GetFolder()
        Dim fileDialog As fileDialog
        Dim strPath As String
        Dim dialogTitle As String
       
        dialogTitle = "Navigate to and select required folder."
       
        Set fileDialog = Application.fileDialog(msoFileDialogFolderPicker)
       
        With fileDialog
            .InitialFileName = "C:\"
            .AllowMultiSelect = False
            .Filters.Clear
            .Title = dialogTitle
            If .Show = False Then
                MsgBox "User cancelled without selecting a folder." & vbCrLf & _
                        "Processing terminated."
                Exit Sub
            End If
             
            strPath = .SelectedItems(1)
         End With
           
        'Some conditions exist where the backslash will already exist _
         with the selection. (Example: If initial path selected.)
        If Right(strPath, 1) <> "\" Then strPath = strPath & "\"
       
        ThisWorkbook.Sheets(1).TextBox1 = strPath   'Edit TextBox1 to your TextBox name
       
        Set fileDialog = Nothing
       
     End Sub
     
     Sub ProcessFiles()
     
        Dim strPropName As String
        Dim wbNew As Workbook
        Dim strPath As String
        Dim strFileName As String
        Dim strProp As String
        Dim lngRow As Long
       
        '***************************************************************************************
        strPropName = "Title"
       
        'Alternative code to use an Inputbox in lieu of hard coding property name.
        'Comment out the line strPropName = "Title" above and uncomment the next line.
        'strPropName = Application.InputBox(Prompt:="Enter the name of the required property.", _
                        Title:="Get property name", Type:=2)
        '***************************************************************************************
       
        strPath = ThisWorkbook.Sheets(1).TextBox1   'Edit TextBox1 to your TextBox name
        Set wbNew = Workbooks.Add
        wbNew.Sheets(1).Cells(1, 1) = "File Name"
        wbNew.Sheets(1).Cells(1, 2) = strPropName
        strFileName = Dir(strPath & "*.pdf")
        lngRow = 1
        Do While strFileName <> ""
            lngRow = lngRow + 1
            wbNew.Sheets(1).Cells(lngRow, 1) = strFileName
            strProp = GetProperty(strPath, strFileName, strPropName)
            wbNew.Sheets(1).Cells(lngRow, 2) = strProp
            strFileName = Dir
        Loop
              
        Windows(wbNew.Name).Activate
        Sheets(1).Activate
        Range("A1:B1").Font.Bold = True
        Columns("A:B").Columns.AutoFit
        Set wbNew = Nothing
     
    End Sub


    Regards, OssieMac

    • Marked as answer by Veerusat Friday, March 23, 2012 5:04 AM
    • Unmarked as answer by Veerusat Friday, March 23, 2012 5:05 AM
    • Marked as answer by Veerusat Friday, March 23, 2012 5:05 AM
    Thursday, March 22, 2012 6:31 AM

All replies

  • Hello Veerusat,

    The following is probably a lot more than you require but it is an example that I have had for some time. It allows you to navigate to the required file and then lists the properties of the file on a worksheet. You should be able to modify if you just want to provide the path and file name without navigating to it.

    I suggest that you copy all of the code below into a new workbook. (Don't use an existing workbook with data because it deletes any existing data from the sheet where it outputs the list of properties.)

    By modifying Sub GetFileName() you get the option of just entering the required property after navigating to and selecting the required file. To modify the code to do this, place a comment (single quote) in front of the following line:

    Call ListMetadata(strPath, strFileName)      'Make list of properties on a worksheet

    and remove the single quote from the next line.

    Copy all of the code between the asterisk lines into the VBA editor.

    If you have problems with the code then please tell me what version of Excel you are using.

    You run the code from the Sub GetFileName()

    Note the following comment and set the reference in the VBA editor or the code will fail. (The reference only gets set for the particular workbook; it does not become a default setting.)

    '*********************************************************************************
    'Select VBA Menu Item Tools -> References and check the box _
     against Microsoft Shell Controls and Automation (Ensure you _
     check the box; not just highlight the line)

    Sub GetFileName()
    'Run this sub. (Edit the Call for desired output)
      Dim fileDialog As fileDialog
      Dim strPathFile As String
      Dim strFileName As String
      Dim strPath As String
      Dim dialogTitle As String
     
      dialogTitle = "Navigate to and select required file."
      Set fileDialog = Application.fileDialog(msoFileDialogFilePicker)
      With fileDialog
          .InitialFileName = ThisWorkbook.Path
          .AllowMultiSelect = False
          .Filters.Clear
          .Title = dialogTitle
          If .Show = False Then
              Exit Sub
          End If
          strPathFile = .SelectedItems(1)
      End With
     
      strFileName = Right(strPathFile, Len(strPathFile) - InStrRev(strPathFile, "\"))
      strPath = Left(strPathFile, Len(strPathFile) - Len(strFileName) - 1)
     
      Set fileDialog = Nothing
     
      Call ListMetadata(strPath, strFileName)      'Make list of properties on a worksheet
      'Call GetProperty(strPath, strFileName)       'InputBox to enter the required property
     
    End Sub


    Sub ListMetadata(fileFolder As String, fileName As String)
        'Makes list of properties on a worksheet
        Dim objShell As Shell32.Shell
        Dim objFolder As Shell32.Folder
        Dim objItem As Shell32.FolderItem
        Dim ws As Worksheet
        Dim r As Long
        Dim strName As String
        Dim varTemp As Variant
       
        Set objShell = New Shell
        Set objFolder = objShell.Namespace(fileFolder)
        Set objItem = objFolder.ParseName(fileName)
        Set ws = Sheets("Sheet1")       'Edit "Sheet1" to your output sheet name
       
        With objFolder
            ws.Columns("A:B").ClearContents   'Clear any existing data
            For r = 1 To 1000
                strName = .GetDetailsOf(objItem.Name, r)
                If strName = "" Then Exit For
                ws.Cells(r, "A") = strName
                varTemp = .GetDetailsOf(objItem, r)
                'varTemp = Replace(varTemp, "?", "", 1)    'xl 2007 was adding question marks to some data
                ws.Cells(r, "B") = varTemp
            Next r
        End With
       
        Set objItem = Nothing
        Set objFolder = Nothing
        Set objShell = Nothing

    End Sub

     

    Sub GetProperty(fileFolder As String, fileName As String)
        'InputBox to enter the required property
        Dim objShell As Shell32.Shell
        Dim objFolder As Shell32.Folder
        Dim objItem As Shell32.FolderItem
        Dim ws As Worksheet
        Dim r As Long
        Dim strName As String
        Dim varTemp As Variant
        Dim arrProperties()
       
        Set objShell = New Shell
        Set objFolder = objShell.Namespace(fileFolder)
        Set objItem = objFolder.ParseName(fileName)
       
        With objFolder
            For r = 1 To 1000
                strName = .GetDetailsOf(objItem.Name, r)
                If strName = "" Then Exit For
                varTemp = .GetDetailsOf(objItem, r)
                If varTemp <> "" Then   'Ignores properties with no value
                    ReDim Preserve arrProperties(1 To 2, 1 To r)
                    arrProperties(1, r) = strName
                    'varTemp = Replace(varTemp, "?", "", 1)    'xl2007 was adding question marks to some data
                    arrProperties(2, r) = varTemp
                End If
            Next r
        End With
       
        Set objItem = Nothing
        Set objFolder = Nothing
        Set objShell = Nothing

        strName = Application.InputBox(Prompt:="Enter the property required", _
                    Title:="Get required property", Type:=2)
        For r = LBound(arrProperties, 2) To UBound(arrProperties, 2)
            If UCase(arrProperties(1, r)) = UCase(strName) Then     'Ignore case for search
                strName = arrProperties(1, r)   'Set case same as property
                Exit For
            End If
        Next r
        If r <= UBound(arrProperties, 2) Then
            MsgBox strName & " = " & arrProperties(2, r)
        Else
            MsgBox strName & " not found or has no value."
        End If

    End Sub

    '*********************************************************************


    Regards, OssieMac

    Sunday, March 18, 2012 2:15 AM
  • Hi OssieMac,

    That was cool..........Thanks for the response but usually title of the document doesnt come under properties of that document.We could see it in a different tab named "Summary".So to retreive that can you help me..................

    though it was not i expected,But it was realy nice.........


    Veeru

    Sunday, March 18, 2012 5:44 PM
  • Hi Veeru,

    Pleased you liked it even if it did not answer the question. From my research it appears that you need to load a dll.

    Google the following and you might find the answer.

    DSO OLE Document Properties Reader 2.1

    Some of the documentation appears to apply to VB and Net and then to Microsoft documents but other documentation appears to indicate it works in VBA and applys to most docuements.

    Following sites might also help.

    http://www.cpearson.com/excel/docprop.aspx

    http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=8422


    Regards, OssieMac

    Monday, March 19, 2012 1:56 AM
  • Hi OssieMac......

    finally got that with few changes here and there in the code.

    So i am fine with it but a small help........Instead of entering the property as "title" in the get property message bos if i need to directly get a tilte if a click a button that what changes should i do in the code.I tried with it but couldn make it.

    And one more point is that i am planning to integrate this code with any other module such that if i specify a directory it should retreive all the names and title of the pdf file present in that directory............

    Can you help me with this ..if so that would be great................

    Thanks in advance


    Veeru

    Tuesday, March 20, 2012 8:07 AM
  • Hi Veeru,

    Firstly what version of xl are you using?

    I haven't tested the code from the links that I gave you; just gave you the links and left all the hard work to you.

    Did you download and install DSO OLE Document Properties Reader 2.1? If so, did you encounter any problems associated with installing DSO OLE Document Properties Reader 2.1?

    Did you use the code from the links?

    Please post the code that you have so far and I will have a look at what needs to be done to achieve your desired result.


    Regards, OssieMac

    Tuesday, March 20, 2012 8:27 AM
  • Hi OssieMac,

    The excel version i'm using is excel-2007.

    I went through the links which you shared but,I couldn make it as I am a beginner.So started reseaching on it and in that process found a clue that if I have adobe acrobat reader 9 th version then this will make the title to be edited.So tried with that in the acrobat 9 version in another machine.So as a result I found that your code was successful in that version without a single change in the code.

    So as of now it would be superb if u modify your code by retreiving the title alone.In addition to it i found the following code which will list you all the pdf file names in a specific directory.

    Sub ListPDF()
        Dim strPath As String
        Dim strFile As String
        Dim doc As Document
        With Application.FileDialog(4) ' msoFileDialogFolderPicker
            If .Show Then
                strPath = .SelectedItems(1)
            Else
                MsgBox "No folder selected.", vbCritical
                Exit Sub
            End If
        End With
        If Right(strPath, 1) <> "\" Then
            strPath = strPath & "\"
        End If
        Set doc = Documents.Add
        strFile = Dir(strPath & "*.pdf")
        Do While strFile <> ""
            doc.Content.InsertAfter strFile
            doc.Content.InsertParagraphAfter
            strFile = Dir
        Loop
    End Sub

    So here comes my question

    1. Can you integrate this code with yours so that if I specify the directory name it has to retreive the name and title of all the pdf files in that specific directory to a word document.

    Hope i had provided enough information.If not then let me know it............

    Thanks in advance.............


    Veeru

    Tuesday, March 20, 2012 9:30 AM
  • Hello again Veeru,

    I have tried and have not been successful in retrieving the Title that matches the Title that I see if I open the pdf file with Adobe Reader and select File -> Properties.

    In my code the Title always comes up empty even though I can see it from Properties in Adobe Reader. Anyway because you said that you have my previously posted code working, I have written some code that you might like to try. I am not familiar with VBA in Word. Therefore I have used Excel and the output is to a new Excel workbook. Your options are to copy the output and paste into a Word document (it becomes a table in Word) or start a new thread in this forum and post the code and ask if someone can translate it from Excel to a Word.

    If the property for the required property name is not found then in the output you will see something like the following: "Title" has no property or is an invalid property name.

    The code between the asterisk lines gives you an option of having the property name hard coded or entering a property name via an InputBox. See my comments in that area of code.

    Run the code from Sub GetFolder()

    Note my following commment re the reference for Shell.

    'Select VBA Menu Item Tools -> References and check the box _
     against Microsoft Shell Controls and Automation (Ensure you _
     check the box; not just highlight the line)

    Sub GetFolder()
        Dim fileDialog As fileDialog
        Dim strFileName As String
        Dim strPath As String
        Dim dialogTitle As String
        Dim wbNew As Workbook
        Dim lngRow As Long
        Dim strPropName As String
        Dim strProp As String
       
        dialogTitle = "Navigate to and select required folder."
       
        Set fileDialog = Application.fileDialog(msoFileDialogFolderPicker)
       
        With fileDialog
            .InitialFileName = "C:\"
            .AllowMultiSelect = False
            .Filters.Clear
            .Title = dialogTitle
            If .Show = False Then
                MsgBox "User cancelled without selecting a folder." & vbCrLf & _
                        "Processing terminated."
                Exit Sub
            End If
             
            strPath = .SelectedItems(1)
            'Some conditions exist where the backslash will already exist _
             with the selection. (Example: If initial path selected.)
            If Right(strPath, 1) <> "\" Then strPath = strPath & "\"
           
            '***************************************************************************************
            strPropName = "Title"
           
            'Alternative code to use an Inputbox in lieu of hard coding property name.
            'Comment out the line strPropName = "Title" above and uncomment the next line.
            'strPropName = Application.InputBox(Prompt:="Enter the name of the required property.", _
                            Title:="Get property name", Type:=2)
            '***************************************************************************************
           
            Set wbNew = Workbooks.Add
            wbNew.Sheets(1).Cells(1, 1) = "File Name"
            wbNew.Sheets(1).Cells(1, 2) = strPropName
            strFileName = Dir(strPath & "*.pdf")
            lngRow = 1
            Do While strFileName <> ""
                lngRow = lngRow + 1
                wbNew.Sheets(1).Cells(lngRow, 1) = strFileName
                strProp = GetProperty(strPath, strFileName, strPropName)
                wbNew.Sheets(1).Cells(lngRow, 2) = strProp
                strFileName = Dir
            Loop
              
        End With
       
        Windows(wbNew.Name).Activate
        Sheets(1).Activate
        Range("A1:B1").Font.Bold = True
        Columns("A:B").Columns.AutoFit
        Set wbNew = Nothing
        Set fileDialog = Nothing
     
    End Sub


    Function GetProperty(fileFolder As String, fileName As String, strName As String)
        'All properties are saved in an array so that the actual name of the _
         property can be found in lieu of using the numbered index of the property.
        Dim objShell As Shell32.Shell
        Dim objFolder As Shell32.Folder
        Dim objItem As Shell32.FolderItem
        Dim ws As Worksheet
        Dim r As Long
        Dim j As Long
        Dim varTemp As Variant
        Dim strTemp As String
        Dim arrProperties()
       
        Set objShell = New Shell
        Set objFolder = objShell.Namespace(fileFolder)
        Set objItem = objFolder.ParseName(fileName)
       
        With objFolder
            For r = 1 To 1000
                strTemp = .GetDetailsOf(objItem.Name, r)
                If strTemp = "" Then Exit For   'At end of properties
                varTemp = .GetDetailsOf(objItem, r)
                If varTemp <> "" Then   'Ignores properties with no value
                    j = j + 1
                    ReDim Preserve arrProperties(1 To 2, 1 To j)
                    arrProperties(1, j) = strTemp
                    arrProperties(2, j) = varTemp
                End If
            Next r
        End With
       
        Set objItem = Nothing
        Set objFolder = Nothing
        Set objShell = Nothing

        For j = LBound(arrProperties, 2) To UBound(arrProperties, 2)
            If UCase(arrProperties(1, j)) = UCase(strName) Then     'Ignore case for search
                GetProperty = arrProperties(2, j)
                Exit For
            End If
        Next j
        If j > UBound(arrProperties, 2) Then
            GetProperty = Chr(34) & strName & Chr(34) & _
                        " has no property or is an invalid property name."
        End If
       
    End Function


    Regards, OssieMac

    • Marked as answer by Veerusat Wednesday, March 21, 2012 9:10 AM
    Wednesday, March 21, 2012 3:23 AM
  • Hi OssieMac,

    Finally got the answer..Thanks alot for ur support and see you soon in another thread..............

    thanks.........


    Veeru

    Wednesday, March 21, 2012 9:11 AM
  • Hi OssieMac,

    Onca again there is a small issue as I need to alter this code in such a manner that if i click a button say [Bowse] it has to retreive the file and place the path of the folder (c:/abc/qwerty/) in a text box.

    Then if i click a button [submit] it has to do the functionality of getting the file name and titles from the folder in the path mentioned in the text box.

    Can you help me with this?


    Veeru

    Thursday, March 22, 2012 4:16 AM
  • Hello Veeru,

    Use Developer Ribbon -> Insert ActiveX TextBox to place a text box on the first sheet of the excel workbook with the VBA code. The TextBox must be TextBox1. Or edit the code to change TextBox1 to the name of your TextBox. (2 places; one in each sub. See comments.)

    Run Sub GetFolder() to Browse for the required folder.

    Run Sub ProcessFiles() to get the Files and Properties.

    Assuming that you have inserted the code into a standard module then use the Form control buttons and assign the required macros to them. (GetFolder and ProcessFiles.)

    Keep the Function GetProperty(......) code from my previous post because it is still required in addition to the following code.

    Sub GetFolder()
        Dim fileDialog As fileDialog
        Dim strPath As String
        Dim dialogTitle As String
       
        dialogTitle = "Navigate to and select required folder."
       
        Set fileDialog = Application.fileDialog(msoFileDialogFolderPicker)
       
        With fileDialog
            .InitialFileName = "C:\"
            .AllowMultiSelect = False
            .Filters.Clear
            .Title = dialogTitle
            If .Show = False Then
                MsgBox "User cancelled without selecting a folder." & vbCrLf & _
                        "Processing terminated."
                Exit Sub
            End If
             
            strPath = .SelectedItems(1)
         End With
           
        'Some conditions exist where the backslash will already exist _
         with the selection. (Example: If initial path selected.)
        If Right(strPath, 1) <> "\" Then strPath = strPath & "\"
       
        ThisWorkbook.Sheets(1).TextBox1 = strPath   'Edit TextBox1 to your TextBox name
       
        Set fileDialog = Nothing
       
     End Sub
     
     Sub ProcessFiles()
     
        Dim strPropName As String
        Dim wbNew As Workbook
        Dim strPath As String
        Dim strFileName As String
        Dim strProp As String
        Dim lngRow As Long
       
        '***************************************************************************************
        strPropName = "Title"
       
        'Alternative code to use an Inputbox in lieu of hard coding property name.
        'Comment out the line strPropName = "Title" above and uncomment the next line.
        'strPropName = Application.InputBox(Prompt:="Enter the name of the required property.", _
                        Title:="Get property name", Type:=2)
        '***************************************************************************************
       
        strPath = ThisWorkbook.Sheets(1).TextBox1   'Edit TextBox1 to your TextBox name
        Set wbNew = Workbooks.Add
        wbNew.Sheets(1).Cells(1, 1) = "File Name"
        wbNew.Sheets(1).Cells(1, 2) = strPropName
        strFileName = Dir(strPath & "*.pdf")
        lngRow = 1
        Do While strFileName <> ""
            lngRow = lngRow + 1
            wbNew.Sheets(1).Cells(lngRow, 1) = strFileName
            strProp = GetProperty(strPath, strFileName, strPropName)
            wbNew.Sheets(1).Cells(lngRow, 2) = strProp
            strFileName = Dir
        Loop
              
        Windows(wbNew.Name).Activate
        Sheets(1).Activate
        Range("A1:B1").Font.Bold = True
        Columns("A:B").Columns.AutoFit
        Set wbNew = Nothing
     
    End Sub


    Regards, OssieMac

    • Marked as answer by Veerusat Friday, March 23, 2012 5:04 AM
    • Unmarked as answer by Veerusat Friday, March 23, 2012 5:05 AM
    • Marked as answer by Veerusat Friday, March 23, 2012 5:05 AM
    Thursday, March 22, 2012 6:31 AM
  • Hi OssieMac,

    That was perfect.........

    Now I have an enhancement based issue on this.Is it possible to revert back the process?

    Say for example if I change the file name or the title in the excel sheet which we had got then it has to revert back the title or the filename of the corressponding pdf file in the source folder.

    so is this possible and if so can you help me with the vba code..............


    Veeru

    Friday, March 23, 2012 5:09 AM
  • Now I have an enhancement based issue on this.Is it possible to revert back the process?

    Say for example if I change the file name or the title in the excel sheet which we had got then it has to revert back the title or the filename of the corressponding pdf file in the source folder.

    Not sure that I fully comprehend. Do you mean rename the file and rename the title in the file property?

    Renaming a file should be possible I think but not sure about writing to the properties of the file. I will have to test. However, will await your reply to confirm that we are on the same wave length.


    Regards, OssieMac

    Friday, March 23, 2012 5:57 AM
  • Hi OssieMac,

    Yup................We are on the same wave length..................

    So can I rename the file and the title as well from the excel sheet we had got as output from the vba code................

    yup.....renaming the title needs to update the file properties...........

    So is that possible .............


    Veeru

    Friday, March 23, 2012 9:00 AM
  • Hello again Veeru,

    Out of my depth. If it is possible to write to the properties then I don't know how.


    Regards, OssieMac

    Saturday, March 24, 2012 6:54 AM
  • Hello OssieMac

    i've tried your code written at the top to read PDF file properties... im using ur "GetProperty" function with correct parameters... the PDF file im supplying contains "Subject" field with some value when i'll see using right clicking and selecting properties from Windows Explorer... but ur function tells that "Subject has no property or invalid property name"... your function shows basic properties correctly e.g. date created, type etc...

    im using Excel 2010 and Windows 7... would u plz help me what is wrong with it...?

    regards,

    Ehtisham

    Monday, May 26, 2014 12:22 PM
  • Hello Ehtisham,

    Does your pdf file contain any sensitive data? If not, perhaps you can post it on OneDrive and I will have a look and see if I can extract the Subject.

    Some guidelines to upload a file to OneDrive.

    1. Go to this link.  https://onedrive.live.com
    2. Use the same login Id and Password that you use for this forum.
    3. Select Upload in the blue bar across the top and browse to the file to be uploaded and select Open (or just double click). (Be patient and give it time to display the file after initially seeing the popup indicating it is done.)
    4. Right click the file on OneDrive and select Share.
    5. Do NOT fill in the form; "Select Get a Link" on the left side.
    6. Click the button "Create a Link"
    7. Click in the box where the link is created and it will highlight.
    8. Copy the link and paste into your reply on this forum.


    Regards, OssieMac

    Tuesday, May 27, 2014 1:40 AM