Solved

Pictures not displaying on sheet

Posted on 2011-02-18
15
411 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
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
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 (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE) 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

717 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