Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2007
    Location
    Massachusetts, USA
    Posts
    272
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Macro needed to export Adobe Acrobat FDF Comments file to Excel

    Hello,

    I am looking for a macro for MS Excel 2010 that can:

    1. Open a .FDF (Acrobat comments) file.
    2. Save out all comments into a spreadsheet column.

    Any ideas on how to make this work is really appreciated.
    I did find a macro out there on a Google search, but it looks like two lines need to be tweaked in the code.

    Here is that code:

    Thanks in advance for any suggestions.

    Regards,

    J
    -----------------------------------------------------------------------------------------------------------------------------------------------

    Code:
    Public Sub DoAdobeImport()
    Dim FName As Variant
    Dim Sep1 As String
    Dim Sep2 As String
    Dim Sep3 As String
    Dim Sep4 As String
    Dim RowNdx As Integer
    Dim ColNdx As Integer
    Dim WholeLine As String
    Dim Pos As Integer
    Dim NextPos As Integer
    Dim StartPos As Integer
    Dim EndPos As Integer
    Dim recordPos As Integer
    Dim recordPos2 As Integer
    Dim SaveColNdx As Integer
    Dim Part1 As String
    Dim Part2 As String
    'get name of FDF file
    FName = Application.GetOpenFilename _
        (filefilter:="Adobe FDF Data Files(*.fdf),*.fdf,All Files (*.*),*.*", Title:="Select FDF file to import")
        
    If FName = False Then
        MsgBox "You didn't select a file"
        Exit Sub
    End If
    'Set record separators
    Application.ScreenUpdating = False
    Sep1 = "<<"
    Sep2 = ">>"
    Sep3 = "/T"
    Sep4 = ")"
    'set cell row and column where to start entering data
    ColNdx = ActiveCell.Column
    RowNdx = ActiveCell.Row
    Open FName For Input Access Read As #1 'open fdf file
    
    
        Line Input #1, WholeLine 'Skip first three lines as they do not contain any data
        Line Input #1, WholeLine
        Line Input #1, WholeLine
        StartPos = (InStr(1, WholeLine, "[")) + 1 ' find where data starts
        EndPos = (InStr(1, WholeLine, "]")) - 1 ' find where data ends
        WholeLine = Mid(WholeLine, StartPos, EndPos) 'capture just the data fields
        Pos = 3 ' set start position    
        NextPos = InStr(Pos, WholeLine, Sep2) 'find end of current record
        While NextPos >= 1
            TempVal = Mid(WholeLine, Pos, NextPos - Pos) 'Find start of next record
            recordPos = (InStr(1, TempVal, Sep4)) 'Go to end of record by using ")"
            'Assume the value is in A1, in B1  =Left(A1,len(A1)-2)
            
            Part1 = Trim(Mid(TempVal, 1, recordPos)) 'get data record name
            
            '*******Comment out the line below if you do not want data record name*****
            Cells(RowNdx, ColNdx).Value = Right((Left(Part1, Len(Part1) - 1)), Len((Left(Part1, Len(Part1) - 1))) - 3) ' trim off start and end superfluous characters and enter in cell
            
            '*******Comment out the line below if you do not want data record name*****
            RowNdx = RowNdx + 1 'move to next row
            
            recordPos2 = (InStr(1, TempVal, Sep4)) ' find ")" which is end of record
            
            Part2 = Trim(Mid(TempVal, recordPos2)) 'get data
            'Check to see if data field is blank
            If Part2 = Sep4 Then
            Cells(RowNdx, ColNdx).Value = ""
            'Check if Data is Yes or No
            ElseIf Right(Part2, 1) <> Sep4 Then 'trim off start and end superfluous characters and enter in cell
            Cells(RowNdx, ColNdx).Value = Right((Left(Part2, Len(Part2) - 0)), Len((Left(Part2, Len(Part2) - 0))) - 4) ' trim off start and end superfluous characters and enter in cell
            Else
            Cells(RowNdx, ColNdx).Value = Right((Left(Part2, Len(Part2) - 1)), Len((Left(Part2, Len(Part2) - 1))) - 4) ' trim off start and end superfluous characters and enter in cell
            End If
            
            'Cells(RowNdx, ColNdx).Value = Trim(Mid(TempVal, recordPos2))   'Second part which contains data
            ColNdx = ColNdx + 1 ' move to next column
            
            '*******Comment out the line below if you do not want data record name*****
            RowNdx = RowNdx - 1 ' move up a row
            
            Pos = NextPos + 4 ' move to start of next record record
            NextPos = InStr(Pos, WholeLine, Sep2) ' find end of next record
            'if more records loop again
        Wend
     'if no more records end
    Close #1
    End Sub
    ----------------------------------------------------------------------------------------------------------------------------------------------

    HTML Code:
    http://forums.scribus.net/index.php?topic=313.0

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Can you attach a couple of PDF files that are formatted in the manner you will be using and also show us the output from each that you want from those files?

    Steve

  3. #3
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    The FDF file format looks like it is a binary file so it is not going to be easy - However...

    You can also export the comments as an XFDF file which is xml. This is much easier to work with and will import directly into Excel.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •