Exporting PDF files from OLE Object fields

[Blaine Manock]

Hi All,

One of my clients asked me to export thousands of PDF files stored within an
OLE Object field within an Access 97 database so they could import them into
another system.
While I was trying to find the solution, I found many other people in the
same situation on the Internet, so I thought I'd post the solution here for
everyone else to read.
The problem with exporting any file (other than images) from OLE Object
fields is that the OLE Object field also contains the OLE header and footer
which need to be removed from the saved file for it to be valid.
To do this for PDF files, use the code below (please note that this only
works for PDFs)

(Credit to PSTRUH Software for providing the funtion for converting binary
streams to text data)

Further enhancements would be to get it to look for the end of the OLE
header (rather than the beginning of the PDF) and the start of the OLE
footer (rather than the end of the PDF).
This then might make it work for all files.

Regards,

Blaine Manock
---------------------------------------------------------------------------
Public Function LoadFileFromDB(ByRef rFiles As ADOR.Recordset, _
ByVal sField As String, _
ByVal sSaveName As String, _
Optional ByVal sSavePath As String = "") As
String

Dim lStream As Long, lPDFStart As Long, lPDFEnd As Long, l As Long

Dim strm As ADODB.Stream, strm2 As ADODB.Stream
Dim sDoc As String

If rFiles(sField).ActualSize = 0 Then Exit Function

Set strm = New ADODB.Stream
strm.Type = adTypeBinary
strm.Open

Set strm2 = New ADODB.Stream
strm2.Type = adTypeBinary
strm2.Open

strm.Write rFiles(sField).Value
lStream = strm.Position 'Store stream length

For l = 0 To lStream

strm.Position = l
If Stream_BinaryToString(strm.Read(4)) = "%PDF" Then
lPDFStart = l
Exit For
End If

Next

If lPDFStart > 0 Then

For l = lStream To lPDFStart + 4 Step -1

strm.Position = l

If Stream_BinaryToString(strm.Read(5)) = "%%EOF" Then

lPDFEnd = l + 6
Exit For

End If

Next

End If

If lPDFStart And lPDFEnd Then

strm.Position = lPDFStart
strm2.Write strm.Read(lPDFEnd - lPDFStart + 1)

If sSavePath = "" Then
sDoc = App.Path & "\"
Else
sDoc = sSavePath & "\"
End If

sDoc = sDoc & sSaveName

strm2.SaveToFile sDoc, adSaveCreateOverWrite
LoadFileFromDB = sDoc

End If

strm.Close
strm2.Close

Set strm = Nothing
Set strm2 = Nothing

End Function

Function Stream_BinaryToString(Binary)

'Create Stream object
Dim BinaryStream As New Stream

If IsNull(Binary) Then Exit Function
' Set BinaryStream = CreateObject("ADODB.Stream")

'Specify stream type - we want To save text/string data.
BinaryStream.Type = adTypeBinary

'Open the stream And write text/string data To the object
BinaryStream.Open
BinaryStream.Write Binary

'Change stream type To binary
BinaryStream.Position = 0
BinaryStream.Type = adTypeText
BinaryStream.Charset = "us-ascii"

'Open the stream And get binary data from the object
Stream_BinaryToString = BinaryStream.ReadText

BinaryStream.Close
Set BinaryStream = Nothing

End Function

 

[John Nurick]

Thanks for that, Blaine. It looks as if it's going to be useful.

Hi All,

One of my clients asked me to export thousands of PDF files stored within an
OLE Object field within an Access 97 database so they could import them into
another system.
While I was trying to find the solution, I found many other people in the
same situation on the Internet, so I thought I'd post the solution here for
everyone else to read.
The problem with exporting any file (other than images) from OLE Object
fields is that the OLE Object field also contains the OLE header and footer
which need to be removed from the saved file for it to be valid.
To do this for PDF files, use the code below (please note that this only
works for PDFs)

[snip]