Solved

Pictures not displaying on sheet

Posted on 2011-02-18
15
409 Views
Last Modified: 2012-06-21
I've the code below to insert pictures on a Excel sheet. The pictures are on a network drive. This works fine and I can see the pictures on my computer when I open the file.
If I save the file on another PC and try to open it, I can't see the pictures and instead of the picture I get the message displayed on the attached image.

I'm working with Excel 2010. Three months ago I was working with Excel 2003 and I didn't had this problem. I only have this problem since our company upgraded to Excel 2010.

Can annyone please help me understand what I'm missing here?

Thanks,

jppinto
Sub InsereFotografia()
On Error Resume Next
   Dim result() As String
   result = Split(ActiveCell.Value, "-")
    
   Dim num As Long
   num = result(0)
   
   ActiveCell.Offset(-2, 0).Select
    
   ActiveSheet.Pictures.Insert( _
        "P:\Backup Montagem Final\ARQUIVO\Agenda MF 2011\FOTOS MF 2011\000" & num & ".JPG").Select
End Sub

Open in new window

Capturar.JPG
0
Comment
Question by:jppinto
  • 7
  • 4
  • 2
  • +2
15 Comments
 
LVL 22

Assisted Solution

by:rspahitz
rspahitz earned 50 total points
ID: 34928543
maybe during the upgrade, the drive mappings changed?  is that other machine's P-drive still the same as yours?
0
 
LVL 33

Author Comment

by:jppinto
ID: 34928580
On the machines that don't see the pictures, they don't have access to that drive.
What I can add to the problem is, when using Excel 2003, when I ran this code, the pictures where embended into the Excel sheet, so the size of the file would grow substancially. Now, I've added like 1000 pictures using the same code and the file still has less that 1Mb! This has happened with other files where I've used this code and only after the Office upgrade.
0
 
LVL 45

Assisted Solution

by:patrickab
patrickab earned 50 total points
ID: 34928591
"P:\Backup Montagem Final\ARQUIVO\Agenda MF 2011\FOTOS MF 2011\000" & num & ".JPG").Select

Is probably no longer a valid path to the picture. Not sure what the 'Select' word is doing there.

Patrick
0
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 33

Author Comment

by:jppinto
ID: 34929301
I've already tryed without the .Select statment and the result is the same. I tryed changing name of the folder where the pictures are and when I open the file, the pictures are gone from the sheet! I change the name of the folder back to it's original name and the pictures appear!
0
 
LVL 33

Author Comment

by:jppinto
ID: 34929303
Also, I'm checking this on my home laptop, not on any company computer.
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 34935837
jppinto,

you say

>> On the machines that don't see the pictures, they don't have access to that drive.

So there's the problem. They don't have access to the drive.

You need to ensure that the source of the files is accessible to the user who is opening the Excel workbook. Whatever version you are using.

>> Also, I'm checking this on my home laptop, not on any company computer.

On your home laptop, can you access the files in Windows Explorer with the "P:\" drive mapping?

If the pictures are not embedded (to save disk space), then of course the user needs to have access to the link location and it needs to have the same drive mapping as the one the linked picture is using.

cheers, teylyn
0
 
LVL 33

Author Comment

by:jppinto
ID: 34936773
Hello teylyn,

Yes, I understand that if I don't have access to the drive where the pictures are, I can't see them on my sheet. They are linked and not embedded on my sheet, but that's my problem...why are they getting linked instead of embedded on the sheet?!? How can I get the pictures to be embedded on the sheet instead of linked? Do I need to change annything on my code? I'm I doing something wrong?

Thanks for your support on this one.

jppinto
0
 
LVL 50

Assisted Solution

by:Ingeborg Hawighorst
Ingeborg Hawighorst earned 200 total points
ID: 34936897
I just recorded a macro with Excel 2010. I inserted an image from my hard drive. The code is

Sub Macro2()
    ActiveSheet.Pictures.Insert( _
        "C:\Documents and Settings\UserName\My Documents\My Pictures\SubFolder\FileName.jpg" _
        ).Select
End Sub

Open in new window


This is not a linked image, but an embedded one. I see that the code generated by the macro recorder is basically identical to the code you posted. I don't know how to specify embedded vs. linked, sorry.

I've found some code for inserting images here: http://www.exceltip.com/st/Insert_pictures_using_VBA_in_Microsoft_Excel/486.html

Sub InsertPicture(PictureFileName As String, TargetCell As Range, _
    CenterH As Boolean, CenterV As Boolean)
' inserts a picture at the top left position of TargetCell
' the picture can be centered horizontally and/or vertically
Dim p As Object, t As Double, l As Double, w As Double, h As Double
    If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub
    If Dir(PictureFileName) = "" Then Exit Sub
    ' import picture
    Set p = ActiveSheet.Pictures.Insert(PictureFileName)
    ' determine positions
    With TargetCell
        t = .Top
        l = .Left
        If CenterH Then
            w = .Offset(0, 1).Left - .Left
            l = l + w / 2 - p.Width / 2
            If l < 1 Then l = 1
        End If
        If CenterV Then
            h = .Offset(1, 0).Top - .Top
            t = t + h / 2 - p.Height / 2
            If t < 1 Then t = 1
        End If
    End With
    ' position picture
    With p
        .Top = t
        .Left = l
    End With
    Set p = Nothing
End Sub

Open in new window


I hope this will help you solve your issue.

cheers, teylyn
0
 
LVL 33

Author Comment

by:jppinto
ID: 34937935
I teylyn,

I also found the code that you posted. No luck again because the picutures also get linked, not embedded!

jppinto
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 34938542
Here's the suggestion from the MS help files (or the not very HELPful FILES):


If you sent your document through e-mail or copied it to a network file server, and your picture or clip is linked (linked object: An object that is created in a source file and inserted into a destination file, while maintaining a connection between the two files. The linked object in the destination file can be updated when the source file is updated.) and not embedded (embedded object: Information (object) contained in a source file and inserted into a destination file. Once embedded, the object becomes part of the destination file. Changes you make to the embedded object are reflected in the destination file.), send the picture or clip files through e-mail with your document, or copy the picture or clip files to the same network file folder that contains your document.


 Tip   In Microsoft Office PowerPoint 2007, you can use the Package for CD feature to update the links and bundle the associated files.


--
One thought is that you can use a VB picturebox that, I think lets you embed the picture.  Then you can move it over when the form is opened.  Seems rather silly to do that, but maybe worth a try.
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 200 total points
ID: 34942688
Try using Shapes.AddPicture instead as that allows you to specify whether you want a link or not.
0
 
LVL 33

Author Comment

by:jppinto
ID: 34945612
This must be an Excel 2010 bug!

You can insert picture and you have the option to "Insert", "Link to File" or "Insert and Link" by clicking on the Insert button on the bottom of the insert dialog box. If you record a macro and insert a picture onto your sheet and select the option "Insert", then insert another picture and select the "Link to File", the code that you get is exactly the same.

If I run my macro on a Excel 2010 computer the pictures appear linked, if I run my macro on an Excel 2003 computer, the pictures are embedded into the Excel file and I can send to people that don't have access to the folder where the original pictures are!

jppinto
Capture.JPG
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 34946298
Just a thought...maybe you have to save it as an xlsb file?
0
 
LVL 33

Author Closing Comment

by:jppinto
ID: 34959560
As this looks like an Excel 2010 bug, and the solutions presented by teylyn and rorya can serve as a walkaround to the problem, I've split the points among the participants on this question to reward your participation. Hope that this is fair for you.
As mentioned before, the way I "solved" the problem was running the macro on a PC with Office 2003. This way the pictures get embedded on the sheet and my problem is solved.

Thanks again for your participation. It let me to find my own solution.

jppinto
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 34962464
Thanks, and good to know about this.  Sounds like the 2007/2010 rewrite of Excel didn't properly handle embedded images.  Hopefully this gets fixed with a patch in the future.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

837 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question