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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
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
NorieAnalyst Assistant Commented:
What type of field is ControlVuelo?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

NorieAnalyst Assistant Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
NorieAnalyst Assistant Commented:
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
NorieAnalyst Assistant Commented:
So what do you want to do exactly?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.