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:
Code:
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:
Code:
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?