Using an Access Table Attachment field as an Excel Header image

Good Morning! I am having trouble with an Excel header problem and am hoping someone can point me in the right direction.

My company's page header needs to be contained in the Access database that the process is being housed in. I have created a table for this purpose. It stores an ID and category and an attachment field. I have only one record and one image stored as the picture I want to use for my excel sheet header. I am using Access and Excel 2007.

First off, I assume the right question is: Can I use a stored object as a header image? Or, is it required to use a file path for this purpose? Are only external images allowed?

Second, how can I reference this object in vba in variable form? Is this an Object variable type?
pdkolmoAsked:
Who is Participating?
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.

suvmitraCommented:
Hi, In my opinion you need to break down the tasks to understand and accomplish the task in a better way.

1. Attachment field: It stores the entire file not just the path, so just review whether you need an attachmnet field in the first place, because it is going to make your application heavy.

2. You want to populate / embed images on your Excel files..for that you need a complete file path reference, so store the files/images into a folder and in  MSAccess DB, populate all full paths of the images in a text field.

3. Create a connection to the Access DB from Excel and write simple SQL statement to pull the file path.

4.  Use a code similar to this sample to insert images

Sub test()

Dim myPict As Picture
Dim curWks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim myPictName As Variant

Set curWks = Sheets(1)   ' Change to suit

curWks.Pictures.Delete

With curWks
    Set myRng = .Range("C2", .Cells(.Rows.Count, "C").End(xlUp))
End With

For Each myCell In myRng.Cells
    If Trim(myCell.Value) = "" Then
        'do nothing
    ElseIf Dir(CStr(myCell.Value)) = "" Then
        'picture not there!
        MsgBox myCell.Value & " Doesn't exist!"
    Else
        With myCell.Offset(0, 3) '3 columns to the right of C (F)
            Set myPict = myCell.Parent.Pictures.Insert(myCell.Value)
            myPict.Top = .Top
            myPict.Width = .Width
            myPict.Height = .Height
            myPict.Left = .Left
            myPict.Placement = xlMoveAndSize
        End With
    End If
Next myCell

End Sub

Open in new window

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
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 Excel

From novice to tech pro — start learning today.