Solved

Pictures not displaying on sheet

Posted on 2011-02-18
15
405 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
Comment Utility
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
Comment Utility
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
Comment Utility
"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
 
LVL 33

Author Comment

by:jppinto
Comment Utility
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
Comment Utility
Also, I'm checking this on my home laptop, not on any company computer.
0
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
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
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 50

Assisted Solution

by:teylyn
teylyn earned 200 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Just a thought...maybe you have to save it as an xlsb file?
0
 
LVL 33

Author Closing Comment

by:jppinto
Comment Utility
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
Comment Utility
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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now