[AccessD] Attachment Data Type

Arthur,

    Sample procedure named P_ExportImgAttachmentsToWord() as given below, 
will export the .jpg type attachments on current record of the form, to 
destination word document. The images get inserted at pre-existing 
sequential bookmarks.

    The bookmarks can be in any alphanumeric series. The first bookmark 
where insertion of images is required to commence, can be passed as an 
argument. If starting bookmark is not specified, it defaults to "Pic001". 
Sample call in click event of form's command button would be as follows 
(SampleWord.doc is the destination word document located in the same folder 
that holds the access db. Fatt is the name of control bound to attachment 
type field):

' Code in form's module:
'=============================
Private Sub CmdExpToWord_Click()
    P_ExportImgAttachmentsToWord Me, _
                    "Fatt", CurrentProject.Path & _
                    "\SampleWord.doc", "Pic001"
End Sub
'=============================

    Note:
    (a) The bookmarks can be any alphanumeric series. For example, if  "Img005" 
is passed as an argument for starting bookmark, the available images will 
get inserted at "Img005", "Img006", "Img007" and so on.
    (b) Simultaneously, copy of each exported image gets stored in the temporary folder.

Best wishes,
A.D. Tejpal
------------

' Code in general module
'=====================================
' Declarations section
Public Const TempFolderName As String = "ZZZTemp"
'--------------------------------------------------------

Sub P_ExportImgAttachmentsToWord( _
                        fm As Access.Form, _
                        ControlName As String, _
                        WordDocPath As String, _
                        Optional StartBkMark As _
                        String = "Pic001")
    On Error Resume Next
    Dim wd As Word.Application
    Dim rst As DAO.Recordset2
    Dim fdAtch As DAO.Field2
    Dim fso As FileSystemObject

    Dim Fpt As String, Cnt As Long
    Dim DocName As String, FmtString As String
    Dim BkMark As String, Pfx As String
    Dim TempFolderPath As String

    Set fso = New FileSystemObject

    Pfx = Fn_Prefix(StartBkMark)
    FmtString = String(Len(StartBkMark) - _
                                                Len(Pfx), "0")
    Cnt = Val(Mid(StartBkMark, Len(Pfx) + 1)) - 1

    ' Set temp folder path
    ' This temp folder is placed within the folder
    ' housing this db and will get created if missing.
    TempFolderPath = CurrentProject.Path & "\" & _
                                            TempFolderName
    If Not fso.FolderExists(Fpt) Then
        MkDir Fpt
    End If

    Set wd = New Word.Application
    wd.Documents.Open WordDocPath

    Set rst = fm.Recordset.Fields( _
                fm(ControlName).ControlSource).Value

    With rst
        .MoveFirst
        Do Until .EOF
            DocName = .Fields("FileName")
            If DocName Like "*.jpg" Then
                Cnt = Cnt + 1
                BkMark = Pfx & Format(Cnt, FmtString)
                ' Set path for temp file
                Fpt = TempFolderPath & "\" & DocName

                ' Delete any such file if existing
                fso.DeleteFile Fpt

                ' Set fdAtch to binary data for the
                ' attachment component
                Set fdAtch = .Fields("FileData")

                ' Save to destn path in temp folder
                fdAtch.SaveToFile Fpt

                ' Insert this image file at proper
                ' bookmark in word doc and exit
                ' if export not successful
                Err.Clear
                wd.Selection.GoTo What:=wdGoToBookmark, _
                                                            Name:=BkMark
                If Err.Number <> 0 Then
                    MsgBox "No more sequential bookmarks available"
                    Exit Do
                End If

                wd.Selection.InlineShapes.AddPicture FileName:= _
                                                Fpt, LinkToFile:=False, _
                                                SaveWithDocument:=True
            End If

            .MoveNext
        Loop
        .Close
    End With

    wd.ActiveDocument.Save
    wd.Quit

    MsgBox "Images attached to current record " & _
            "have been exported to " & _
            WordDocPath & vbCrLf & vbCrLf & _
            "Copies also extracted to folder " & _
            TempFolderName & vbCrLf & _
            "(Placed in the main folder housing this db)"

    Set wd = Nothing
    Set fdAtch = Nothing
    Set rst = Nothing
    Set fso = Nothing
    On Error GoTo 0
End Sub
'-----------------------------------------------------

Function Fn_Prefix(ByVal Fdv As Variant) As String
    ' Returns The Vaue Of Non-Numeric Prefix
    ' In Field Value Fdv
    On Error Resume Next
    Dim Pfx As String, Pos As Integer, Cnt As Integer, Txt As String

    Pfx = ""
    Fdv = Fdv & ""
    If Len(Fdv) > 0 Then
    Else
        GoTo ExitPoint
    End If      ' Len(Fdv) > 0

    Pos = 1
    Do While Pos <= Len(Fdv)
        If IsNumeric(Mid(Fdv, Pos, 1)) Then
            Exit Do
        End If
        Pos = Pos + 1
    Loop

    If Pos > 1 Then
        Pfx = Left(Fdv, Pos - 1)
    End If   ' Pos > 1

ExitPoint:
    Fn_Prefix = Pfx
    On Error GoTo 0
End Function
'=============================

----- Original Message ----- 
From: Arthur Fuller
To: Access Developers discussion and problem solving
Sent: Wednesday, June 02, 2010 18:47
Subject: [AccessD] Attachment Data Type

I have been playing with the Attachment data type, introduced in Access
2007. Using the samples provided by Helen Fedemma and A.D. Tejpal, I have
learned how to perform the basic operations (insert, remove, extract, and
open in original app), but now I want to go a step further.

Assume a Word document containing bookmarks which are populated from Access
code. I have all that working fine. But now I want to auto-populate four new
bookmarks (call them Pic1...4) with photos that are stored as Attachments on
the Access record of interest. I have no clue how to achieve this.

Any help or pointer to a sample will be much appreciated.

TIA,
Arthur