Read attachment from Access table and insert into excel as embedded files...

I would like to read some attachments from an access table and insert them into an excel file as embedded objects (so that i can just click on them to see the content of the file). The attachments are either pdf or jpg files. To read the attachment in the file I have the following code:

      Set fldAttach = xRecord.Fields("ControlVuelo")

Is this correct and what is the best way to insert it into an excel file? Pls note that there might be up to two attachments..
Thank you so much for your help!
mpimAsked:
Who is Participating?
 
NorieVBA ExpertCommented:
If the field is an attachment field this is not going to be straightforward.

For a start you'll need code to save the attachments, here's a 'simple' example.

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim fldAttachments As DAO.Field2
Dim rstAttach As DAO.Recordset2

    Set db = CurrentDb

    Set rst = db.OpenRecordset("tblAttachTest")
    Set fldAttachments = rst.Fields("ControlVuelo")

    Do While Not rst.EOF

        Set rstAttach = fldAttachments.Value

        Do While Not rstAttach.EOF

            rstAttach.Fields("FileData").SaveToFile "C:\Test\" & rstAttach.Fields("FileName")
            
            rstAttach.MoveNext

        Loop
        
        rst.MoveNext
        
    Loop

Open in new window


This saves all the attachments from a field named ControlVuelo , which is in a table named tblAttachTest, to a folder named Test.

Once saved the next step would be to embed them in Excel.
0
 
NorieVBA ExpertCommented:
To start with I think you might need to loop through fldAttach to get the values (filenames/paths/urls?) for each attachment.

You would then need code to embed them as OLE objects into an Excel  worksheet.

Have you checked what fldAttach actually is after you run the posted code?
0
 
mpimAuthor Commented:
Hi Norie, sorry but I have never before worked with attachments. How can I read a field from a access table which contains attachments? And how can I access the different elements (Filename, paths, urls)? Thank you so much for any help..
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
NorieVBA ExpertCommented:
What type of field is ControlVuelo?
0
 
mpimAuthor Commented:
Thank you so much. This is terrific help! - I hate to ask but how can i then read it and load into excel as an embedded file? Sorry....
0
 
NorieVBA ExpertCommented:
What exactly are you trying to do?

Do you really want an Excel file with a whole bunch of embedded objects in it?

That's likely to be quite a hefty file when your finished embedding everything.:)

Why not save all the attachments to a folder and set up hyperlinks to them on an Excel worksheet?

The user would still be able to access all the files and they wouldn't be encumbered with a large, probably slow-loading, Excel file.
0
 
mpimAuthor Commented:
Hi Norie, you are absolutely right. Both options would be find with me but it just a register which I will use once and then never again.. Again, thank you so much for your help..
0
 
NorieVBA ExpertCommented:
So what do you want to do exactly?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.