• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 516
  • Last Modified:

Using DB entry to create an Excel Image, ActiveX Image Control

Experts,

I have an Access 2007 Database in which a particular form runs a lot of Office Automation. In a certain table, there is a file path stored to an image location (e.g. "C:\myfolder\myimage1.bmp".) From a form, I open a certain Excel document, which will contain many ActiveX Image Controls. I already have all Excel references in the Access form's VBA. I can get the document open, but everything I have tried or researched to use the path to dynamically create the image in Excel fails. My first ActiveX image control is named "Image1". How can I update the Picture property from Access so the picture will show? Variations of the code below I have tried.
Dim excelApp As Excel.Application
Set excelApp = CreateObject("Excel.Application")
excelApp.DisplayAlerts = False
excelApp.Visible = True
Dim WB As Excel.Workbook
Set WB = excelApp.Workbooks.Open("\\192.168.200.11\Advisor CRM\PFS\PFS Graph Book.xlsx")
WB.Worksheets("Program Summary - IRE").Shapes.OLEObjects("Image1").Object.Picture = LoadPicture(DLookup("[ImagePath]", "CompanyProducts", "[Company] = 'Allianz'"))

Open in new window

0
exalkonium
Asked:
exalkonium
  • 7
  • 3
  • 2
1 Solution
 
c0ldfyr3Commented:
What exactly is happening when you run the code below? Try running this and see if the path is correct...

Dim excelApp As Excel.Application
Set excelApp = CreateObject("Excel.Application")
excelApp.DisplayAlerts = False
excelApp.Visible = True
Dim WB As Excel.Workbook
Set WB = excelApp.Workbooks.Open("\\192.168.200.11\Advisor CRM\PFS\PFS Graph Book.xlsx")
 
Dim sPath As String
sPath = DLookup("[ImagePath]", "CompanyProducts", "[Company] = 'Allianz'")
Debug.Print sPath
Debug.Print WB.Worksheets("Program Summary - IRE").Shapes.OLEObjects("Image1").Name
WB.Worksheets("Program Summary - IRE").Shapes.OLEObjects("Image1").Object.Picture = LoadPicture(sPath)

Open in new window

0
 
exalkoniumAuthor Commented:
Path is correct. When I put the path in manually in a run dialog box the picture opens.

Here is the error:

Method 'Picture' of object 'IImage' failed. That is all I know.
0
 
exalkoniumAuthor Commented:
Sorry, after better reading your question I did what you asked. You statements didn't work (invalid methods and properties of the object.) But I changed them and could get some information but still couldn't change the picture:
Dim WS As Excel.Worksheet
Set WS = WB.Worksheets("Program Summary - IRE")
 
'This works:
MsgBox WS.OLEObjects("Image1").Name
 
'This doesn't:
WS.OLEObjects("Image1").Picture = LoadPicture(sPath)
'Error: Object does not exist
 
WS.OLEObjects("Image1").Object.Picture = LoadPicture(sPath)
'Error: Method 'Picture' of object 'IImage' failed

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
c0ldfyr3Commented:
Can you tell me exactly what control it is you're adding? I can't find one in Excel which gives me the same control name, Picture1 is all I'm coming up with..
0
 
exalkoniumAuthor Commented:
It is an ActiveX Image control, not a form control. When I add a generic one, it says Image1.
0
 
exalkoniumAuthor Commented:
It is on the Developer tab, Controls group, click Insert, at the bottom.
0
 
c0ldfyr3Commented:
Can you please attach your xls without any code? Just the xls with the picture, I don't seem to have the same version but i can have a look and have it fixed in 20 seconds if i can get my hands on it =)

You may need to rename the xls file to .txt or something first...
0
 
exalkoniumAuthor Commented:
I have created a spreadsheet with just the page I need to work on with the images included. Thanks for taking the time to try this.
book.xls
0
 
Antagony1960Commented:
I think this may be a bug in Excel's automation methods as I'm pretty sure you can do this in Word documents without getting the IImage error.

One workaround you could use, if it's practical to do so, is to add a public function to the Excel documents, to do the work, and call it from the Access macro.

Put this sub in the Excel documents:

Public Sub SetPicture(ByVal sSheet as String, ByVal sPath As String, ByVal sItem As String)
    ActiveWorkbook.Worksheets(sSheet).OLEObjects(sItem).Object.Picture = LoadPicture(sPath)
End Sub

And call it from Access with something like this:

    WB.Application.Run "SetPicture", "Program Summary - IRE", sPath, "Image1"

I think it may even be possible to write a function in the Access module to check whether the SetPicture sub already exists in the Excel document, and create it if it doesn't. But I haven't done anything like that myself so I'm not sure how you'd go about it.
0
 
exalkoniumAuthor Commented:
Antagony, you are amazing! I have been trying to solve this puzzle for days! You are my new hero!
0
 
exalkoniumAuthor Commented:
Superb solution!
0
 
Antagony1960Commented:
Glad to have helped. :-)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 7
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now