Using Shapes from Excel within Access VBA

[mcomp72]

Hey everyone. I'm attempting to send some information from one of my queries onto an Excel worksheet. I've been able to send the data (text, numbers, etc), but in one of my fields, I have a file name. The file is an image (PNG) file. I want to insert that image onto the Excel sheet in a certain position.



I know how to do it from within Excel. But when I use the same VBA code in Access, it doesn't work, because Access doesn't let me use Shape as a variable type.

I know that I can go into Tools -> References and turn on the Microsoft Excel Object Library to get it to work, but I was reading another forum post, and one person highly recommended NOT doing this. Their reasoning was that using a reference can easily cause your code to break if there's a different version of Excel installed, or if it's installed in a different location. I'd rather avoid this if possible, because if I were to upgrade to a new version of Excel in the future, I wouldn't want the code to stop working.

I have been able to open the Excel file by creating an Object variable type and assigning it this way:

Set XL = CreateObject("Excel.Application")
XL.Workbooks.Open (CurrentProject.Path & "\my file name here")

However, I can't figure out how to deal with Shapes since I can't assign a variable to the 'Shape' type.

Here's what I'm trying to do:

Dim EmployeeSigImage As Shape
Set EmployeeSigImage = .ActiveSheet.Shapes.AddPicture(variable that holds the path & file name, False, True, 2, 602, -1, -1)

Do you know if there is a way to do this without turning on Microsoft Excel Object Library?

[June7]

Did you try dimming the variable as Object same as I expect you did with the other Excel objects?

Issue with early binding is really backwards compatibility - distributing to a user who has older version of software, not newer.

So if you upgrade, the early binding should still work. If you want the library reference to be the most current then change it and distribute new copy of database to users, but not until all your users are upgraded.

Use late binding if you intend to distribute to public and don't want to support those users (they 'use at own risk') or if you always have users running different versions of Access or if Access version is not in sync with Excel. It is possible to load older version of Access along with latest version of other Office apps.

[mcomp72]

Did you try dimming the variable as Object same as I expect you did with the other Excel objects?

I could have sworn I tried this and it didn't work. However, I just tried it again, and it DID work this time. My syntax must have been slightly off the first time.

Thanks for the info on early vs. late binding. That's great to know.