By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 2,646 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

How to export attached jpeg files from Access to Word

P: 4
I have working VBA that creates Word documents from Access data and can even insert jpeg's into the headers, but the jpegs are in a folder on the C: drive.

What is the VBA to insert the same jpegs that are now attachments in the Access database? The customer wants the jpegs in the database so it can move from machine to machine without reassigned the C: folder path for the jpegs.

Thanks,
May 30 '17 #1

✓ answered by ADezii

  1. I am not sure how you could 'directly' insert Attachments consisting of *.jpgs into a Word Document. As the others have suggested, it would more than likely involve a Binary Transfer of the Images using some form of BLOB approach.
  2. I do, however, have an indirect approach using the SaveToFile Method which will transfer the Attached *.jpgs as Binary Streams to a Folder of your choosing. Once they have been saved to Disk it should be a simple matter to Insert them into a Word Document then subsequently Delete them.
  3. First, a couple of assumptions:
    1. I created a Table named tblTest that consists of only two Fields, namely: [PK] - {AutoNumber/Primary Key}, and [MyJPGs] - {Attachment/holds *.jpgs}.
    2. There are only two Records in this Table: Record #1 has a Primary Key of 1 with four *.jpgs Attached to the [MyJPGs] Field and Record #2 has a Primary Key of 2 with three *.jpgs Attached to the [MyJPGs] Field.
  4. The following Code will extract the four *.jpgs in Record #1 to the CurrentProject.Path Folder. From this point, they can easily be inserted into a Word Document, either manually or programmatically.
  5. Code Definition:
    Expand|Select|Wrap|Line Numbers
    1. Dim dbs As DAO.Database
    2. Dim rst As DAO.Recordset2
    3. Dim rsA As DAO.Recordset2
    4. Dim fld As DAO.Field2
    5. Dim strSQL As String
    6.  
    7. strSQL = "SELECT * FROM tblTest WHERE [PK]=1"
    8.  
    9. 'Get the Database, Recordset, and Attachment Field
    10. Set dbs = CurrentDb
    11. Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
    12. Set fld = rst("MyJPGs")
    13.  
    14. 'Navigate through the Table
    15. Do While Not rst.EOF
    16.   'Get the Recordset for the Attachments field
    17.   Set rsA = fld.Value
    18.     With rsA
    19.       'Save all Attachments in the Field to Disk
    20.       Do While Not rsA.EOF
    21.         ![FileData].SaveToFile CurrentProject.Path & "\" & ![Filename]
    22.           .MoveNext
    23.       Loop
    24.  
    25.       .Close
    26.      End With
    27.  
    28.     rst.MoveNext    'Next Record
    29. Loop
    30.  
    31. rst.Close
    32. dbs.Close
    33.  
    34. Set fld = Nothing
    35. Set rsA = Nothing
    36. Set rst = Nothing
    37. Set dbs = Nothing
    38.  
  6. Any questions, feel free to ask.

Share this Question
Share on Google+
10 Replies


NeoPa
Expert Mod 15k+
P: 31,273
I've heard of BLOBs (Binary Large OBjects) that can be handled in Access, but don't know much about them. It may be the direction to start looking in though.
May 30 '17 #2

PhilOfWalton
Expert 100+
P: 1,430
I Know Stephen Lebans certainly http://www.lebans.com/
has done works on Blobs, I can't remember which was the relevant title, but I have and old Mdb version of one of his programs.

Stephen sadly retired about 8 years ago, so I think most of his work was in Mdb format.

Phil
May 30 '17 #3

P: 4
I'll check it out. Thanks for the replies NeoPa and PhilOfWalton.
May 30 '17 #4

ADezii
Expert 5K+
P: 8,607
  1. I am not sure how you could 'directly' insert Attachments consisting of *.jpgs into a Word Document. As the others have suggested, it would more than likely involve a Binary Transfer of the Images using some form of BLOB approach.
  2. I do, however, have an indirect approach using the SaveToFile Method which will transfer the Attached *.jpgs as Binary Streams to a Folder of your choosing. Once they have been saved to Disk it should be a simple matter to Insert them into a Word Document then subsequently Delete them.
  3. First, a couple of assumptions:
    1. I created a Table named tblTest that consists of only two Fields, namely: [PK] - {AutoNumber/Primary Key}, and [MyJPGs] - {Attachment/holds *.jpgs}.
    2. There are only two Records in this Table: Record #1 has a Primary Key of 1 with four *.jpgs Attached to the [MyJPGs] Field and Record #2 has a Primary Key of 2 with three *.jpgs Attached to the [MyJPGs] Field.
  4. The following Code will extract the four *.jpgs in Record #1 to the CurrentProject.Path Folder. From this point, they can easily be inserted into a Word Document, either manually or programmatically.
  5. Code Definition:
    Expand|Select|Wrap|Line Numbers
    1. Dim dbs As DAO.Database
    2. Dim rst As DAO.Recordset2
    3. Dim rsA As DAO.Recordset2
    4. Dim fld As DAO.Field2
    5. Dim strSQL As String
    6.  
    7. strSQL = "SELECT * FROM tblTest WHERE [PK]=1"
    8.  
    9. 'Get the Database, Recordset, and Attachment Field
    10. Set dbs = CurrentDb
    11. Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
    12. Set fld = rst("MyJPGs")
    13.  
    14. 'Navigate through the Table
    15. Do While Not rst.EOF
    16.   'Get the Recordset for the Attachments field
    17.   Set rsA = fld.Value
    18.     With rsA
    19.       'Save all Attachments in the Field to Disk
    20.       Do While Not rsA.EOF
    21.         ![FileData].SaveToFile CurrentProject.Path & "\" & ![Filename]
    22.           .MoveNext
    23.       Loop
    24.  
    25.       .Close
    26.      End With
    27.  
    28.     rst.MoveNext    'Next Record
    29. Loop
    30.  
    31. rst.Close
    32. dbs.Close
    33.  
    34. Set fld = Nothing
    35. Set rsA = Nothing
    36. Set rst = Nothing
    37. Set dbs = Nothing
    38.  
  6. Any questions, feel free to ask.
May 30 '17 #5

P: 4
ADezii, I was considering something like this. Your code is cleaner than what I was working. :)

Thanks!
May 30 '17 #6

ADezii
Expert 5K+
P: 8,607
  1. I had some spare time, so I decided to write some follow-up Code should you decide to use the prior approach. I found the easiest method to Insert Graphics (*.jpg) once the *.jpgs are written to Disk is to have a Word Template with pre-defined Bookmarks. In my simple Demo:
    1. A Word Template was created as such:
      Expand|Select|Wrap|Line Numbers
      1. C:\Test\Test.docx
    2. Two *.jpgs exist in the C:\Test Folder, namely:
      Expand|Select|Wrap|Line Numbers
      1. JPG1.jpg and JPG2.jpg
    3. Two Bookmarks were also created and strategically placed - they are named Bookmark1 and Bookmark2.
  2. The following Code will insert JPG1.jpg @ Bookmark1 and JPG2.jpg @ Bookmark2. Both Images were scaled to 25% both Horizontally and Vertically to ensure they fit on the Page.
    Expand|Select|Wrap|Line Numbers
    1. Dim wrdApp As New Word.Application
    2. Dim wrdDoc As New Word.Document
    3. Dim objWdRange As Word.Range
    4. Dim strJPG As String
    5. Dim strJPG2 As String
    6. Dim wrdPic As Word.InlineShape
    7.  
    8. strJPG = "C:\Test\JPG1.jpg"
    9. strJPG2 = "C:\Test\JPG2.jpg"
    10.  
    11. wrdApp.Visible = True
    12.  
    13. Set wrdDoc = wrdApp.Documents.Open("C:\Test\Test.docx")
    14.  
    15. With wrdDoc
    16.   Set wrdPic = .Bookmarks("Bookmark1").Range.InlineShapes.AddPicture(Filename:=strJPG, _
    17.                 LinkToFile:=False, SaveWithDocument:=True)
    18.       wrdPic.ScaleHeight = 25
    19.       wrdPic.ScaleWidth = 25
    20.   Set wrdPic = .Bookmarks("Bookmark2").Range.InlineShapes.AddPicture(Filename:=strJPG2, _
    21.                 LinkToFile:=False, SaveWithDocument:=True)
    22.       wrdPic.ScaleHeight = 25
    23.       wrdPic.ScaleWidth = 25
    24.        .SaveAs "C:\Test\Test.docx"
    25. End With
    26.  
    27. wrdDoc.Close
    28. Set wrdDoc = Nothing
    29.  
    30. wrdApp.Quit
    31. Set wrdApp = Nothing
  3. The Code is only meant to be a guide and easily can be improved upon and/or expanded.
  4. It was meant to be an extension of saving the *.jpgs to Disk Code using SaveToFile. It has been tested and is fully operational.
  5. Good Luck with your Project.
May 30 '17 #7

P: 4
ADezii, thanks for taking the time to post this.
May 30 '17 #8

ADezii
Expert 5K+
P: 8,607
You are quite welcome!
May 30 '17 #9

NeoPa
Expert Mod 15k+
P: 31,273
Nice one again Armund.

@CapsFan.
You won't go too far wrong once Armund has got involved. An expert of long standing.
May 30 '17 #10

ADezii
Expert 5K+
P: 8,607
@NeoPa:
Thank you for your kind words. Actually, I learned a lot from you and you are still my Mentor.
May 31 '17 #11

Post your reply

Sign in to post your reply or Sign up for a free account.