vbaexcelacrobatfdf

Acrobat cannot read .FDF written with Excel VBA


I exported a PDF with form fields in FDF and wrote a sub to output another FDF verbatim, with cell values for the form field values. If I edit the FDF in a text editor and change the values, Acrobat can read the file just fine, but the file output with VBA throws an error:

Adobe could not open whatever.fdf because it is either not a supported file type or because the file has been damaged

I've tried two different types of line breaks, I've tried a similar sub with xfdf formatting which is slightly different with the same results.

Sub something()

Dim sht As Worksheet
Set sht = Sheets("owssvr")

Dim lastrow As Integer
lastrow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Dim Fileout As Object

Dim x As Integer
For x = 2 To lastrow
    Set Fileout = fso.CreateTextFile("C:\Users\blabla\" & x & ".fdf", True, True)
                Fileout.Write "%FDF-1.2" & vbCrLf & _
                "%âãÏÓ" & vbCrLf & _
                "1 0 obj" & vbCrLf & _
                "<</FDF<</F(MyDocument.pdf)/Fields[<</T(Adobe Form Field)/V(" & sht.Range("U" & x) & ")>>]/ID[<4ED54800AC4A3D41ABE4F4C7B12A3D23><609E705B7532334B8F914CFF4C09F2A0>]/UF(MyDocument.pdf)>>/Type/Catalog>>" & vbCrLf & _
                "endobj" & vbCrLf & _
                "trailer" & vbCrLf & _
                "<</Root 1 0 R>>" & vbCrLf & _
                "%%EOF" & vbCrLf
Fileout.Close

Next x

End Sub

Solution

  • As much as I want to leave this open in hopes somebody figures out WHY VBA is screwing up the unicode, my problem can be solved without invoking fso at all and just using FreeFile and string replace on an original FDF

    Sub blabla()
    
    Dim objAcroApp As Acrobat.AcroApp
    Dim objAcroAVDoc As Acrobat.AcroAVDoc
    Dim objAcroPDDoc As Acrobat.AcroPDDoc
    Dim jsObj As Object
    Dim boResult As Boolean
    Dim oldPDF As String
    Dim NewFilePath As String
    
        Dim sTemp As String
        Dim iFileNum As Integer
        Dim oldFDF As String
        Dim i As Integer
        Dim lastRow As Integer
        Dim sht As Worksheet
        Set sht = Sheets("owssvr")
    
        With sht
            lastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        End With
    
        For i = 2 To lastRow
    
        oldPDF = "\mydoc.pdf"
        oldFDF = "\mydoc_data.fdf"
        newPDF = "\" & i & ".pdf"
    
        iFileNum = FreeFile
        Open oldFDF For Input As iFileNum
    
        Do Until EOF(iFileNum)
        Line Input #iFileNum, sBuf
        sTemp = sTemp & vbCrLf
        Loop
        Close iFileNum
    
        sTemp = Replace(sTemp, "<</T(some form field)/V( )>>", "<</T(some form field)/V(" & sht.Range("E" & i) & ")>>")
    
        iFileNum = FreeFile
        oldFDF = "\" & i & ".fdf"
        Open oldFDF For Output As iFileNum
    
        Print #iFileNum, sTemp
    
        Close iFileNum
    
                Set objAcroApp = CreateObject("AcroExch.App")
                Set objAcroAVDoc = CreateObject("AcroExch.AVDoc")
                boResult = objAcroAVDoc.Open(oldPDF, "")
                Set objAcroPDDoc = objAcroAVDoc.GetPDDoc
                Set jsObj = objAcroPDDoc.GetJSObject
                jsObj.ImportAnFDF oldFDF
                jsObj.SaveAs newPDF
                boResult = objAcroAVDoc.Close(True)
                boResult = objAcroApp.Exit
    
    Next i
    
    
    End Sub