1

I would like to insert a picture (.jpg,.png) into an Excel sheet from an attachment' field from Access.

My current code is something like this:

Sub InsertPicFromAccessDB()

    Dim con As ADODB.Connection
    Dim rs As ADODB.Recordset
    
    Set con = New ADODB.Connection
    Set rs = New ADODB.Recordset
    
    con.ConnectionString = ConStrAccess
    con.Open
    
    
    With rs
        .ActiveConnection = con
        .Source = "SELECT ProdutoFoto FROM tblProduto WHERE ProdutoFoto=2163150;"
        .LockType = adLockOptimistic
        .CursorType = adOpenKeyset
        .Open
        
    End With
    
    shStockNovo.Range("A57").Value = rs.Fields(0).Value
    rs.Close
    con.Close
End Sub

It doesn't upload the image from the DB.

I already searched for other alternatives and one sugestion was using the ADODB.Sream. But it gave me an error on the "ADODB.Stream.Write" (with my research, no one could fix this problem). It was the "Run Time Error ‘3001’ Arguments are the wrong type, are out of acceptable range, or are in conflict with one another."

GSD
  • 1,252
  • 1
  • 10
  • 12
Corneliu
  • 21
  • 6
  • Have to extract image to an external folder location and load image from there. Common topic. – June7 May 13 '21 at 19:58
  • How can I extract the image to an external folder location? – Corneliu May 13 '21 at 20:06
  • As I said, common topic. Google it and you will find many discussions and examples. – June7 May 13 '21 at 20:08
  • That's what I did, the majority of it only talks about inserting the pictures into the DB. Not extracting. That's why I came here. I only come to forums as last resource when I really can't find a solution. – Corneliu May 13 '21 at 20:20
  • https://stackoverflow.com/questions/39382384/how-to-export-attachments-images-with-a-given-name-to-a-folder and https://learn.microsoft.com/en-us/office/vba/access/Concepts/Data-Access-Objects/work-with-attachments-in-dao and https://www.fmsinc.com/MicrosoftAccess/modules/code/AccessDatabases/Fields/AttachmentFields.htm – June7 May 13 '21 at 20:30
  • How can I use this line: "rsPictures.Fields("FileData").SaveToFile fName" in Excel VBA? There's no argument "savetofile"for the Fields object – Corneliu May 13 '21 at 20:58
  • If code is in Excel VBA, possibly have to declare, set, manipulate Access objects for the image export. – June7 May 13 '21 at 21:42
  • Yes, it is in Excel VBA. I don't know how to manipulate the Access objects. – Corneliu May 13 '21 at 23:02
  • https://www.freevbcode.com/ShowCode.asp?ID=1802 This is an example of using stream, but as I told before. I get the error on stream.write line. – Corneliu May 13 '21 at 23:11
  • That article has code for pulling images from SQLServer db Image type field, not Access attachment field. Code is not relevant. – June7 May 14 '21 at 03:44

1 Answers1

0

Code in Excel VBA to extract an image file from Access attachment field and save to folder then embed in Excel worksheet.

  1. first set a reference for Microsoft DAO x.x Object Library or Microsoft Office x.x Access Database Engine Object Library

  2. sample code

Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim rs As DAO.Recordset, rsP As Variant, strFile As String
Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase("C:\path\filename.accdb")
Set rs = db.OpenRecordset("SELECT ProdutoFoto FROM tblProduto WHERE ProdutoFoto=2163150")
Set rsP = rs.Fields("ProdutoFoto").Value
rsP.Fields("FileData").SaveToFile "C:\Path"
strFile = rsP.Fields("FileName")
shStockNovo.Range("A57").Select
shStockNovo.Pictures.Insert ("C:\path\" & strFile)
June7
  • 19,874
  • 8
  • 24
  • 34
  • Unfortunately I've tried that and I get an error in loading DLL. I have ADO 6.1 Library, VB Script Regular Expressions, Outlook, Internet Controls and HTML object library references checked. I don't think that could interfere with the error. – Corneliu May 14 '21 at 18:39
  • I got it. I have to refer to the Microsoft Office 16.0 Access Database Engine Object Library. – Corneliu May 14 '21 at 20:06
  • I did not have to select that library. So just tested and works with either library selected. – June7 May 14 '21 at 21:12