?
Solved

How to automatically Insert Images in Excel but have the images show up Offline?

Posted on 2011-05-11
28
Medium Priority
?
1,757 Views
Last Modified: 2012-05-11
Hi All,

I have a simple but complex problem. I want to insert web images in my excel spreadsheet automatically. I am using a simple VBA code

Sub Macro1()
'
    Dim SelRange As Range
    Set SelRange = Selection
    sImageName = SelRange.Value
    ActiveSheet.Pictures.Insert(sImageName).Select
    Selection.ShapeRange.Height = 80
    SelRange.Value = ""
End Sub

What this does is it takes the file path in the cell and inserts the image. The images are often from an internet source.

however, If i'm offline and not on the internet, when I open the excel spreadsheet, the images will not show up locally. Where the images are it will just display the source of that image is unavailable.

What really makes this all confusing is if I MANUALLY insert an image, via Excel Insert Picture function, and I would paste it the same online image source, this picture would show up Offline and when i'm NOT on the internet.

I need to be able to automatically insert an image via VBA and have it show up if i'm offline. Can someone please help with this?

Thanks.
0
Comment
Question by:bsun711
  • 16
  • 11
28 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 35743296
After you manually insert, is the picture still active?  e.g., does the link update if the picture changes?

I'm guessing not.  You might need to write a macro to open all your picture links and save them as JPEG or other alternative, then your links would be to the saved file.

Does this help?

dave
0
 

Author Comment

by:bsun711
ID: 35743816
After you manually insert, I think the picture becomes part of the file, and is not dependent on the original source. Because when I manually inserted, and turned off the internet, I was still able to open the excel file and see the image.

Are you talking abt writing the macro in excel? or outside of excel? I don't think your solution is plausible inside excel. Opening an image inside excel is basically inserting an image with a macro, and that's the problem that I am having.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36017394
I must have missed your message, and just caught it backtracking through unclosed posts.  I was recommending doing it with a macro, as you said that doing it manually made it work correctly.  >>if I MANUALLY insert an image, via Excel Insert Picture function, and I would paste it the same online image source, this picture would show up Offline and when i'm NOT on the internet.


Please let me know if you need further assistance.

Dave
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 

Author Comment

by:bsun711
ID: 36017624
It does not work with a macro...i had posted my macro in my first post. However if you do it manually like by going to the menu and stuff it works. You can try it out yourself and see what i mean.

Thanks for your help.

Brian
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36020058
I mean the macro to open all your links and save them as JPEG files, so when you're offline it doesn't matter.

Send me a link to a picture you have a problem with and I can simulate what you're finding and demonstrate proposed solutions.

Dave
0
 

Author Comment

by:bsun711
ID: 36020216
I don't think there is a function where you can save the image into a jpg file in excel is there? If this is possible it would actually be really useful too.

But for me, it's having the jpg saved IN the excel spreadsheet, i think excel saves it as an object or something. not sure.

If you like to try it out, insert any image from the web with a macro, save and close your spreadsheet. turn the internet off, and reopen the spreadsheet, then you'll see what i mean. And then if you try to insert the picture but from the excel menu, it'll be a different result.

thanks,
brian
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36020483
You're not making it easy :).  


How are you getting the picture from the internet to a file on your hard disk that you can then manually (or programmaticaly) insert?  Just send me one of those, lol.

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36020487
I am good at what I do, but not so good figuring out exactly what YOU are doing.  A little help, please.

:)

Dave
0
 

Author Comment

by:bsun711
ID: 36020645
Ok, let say you are in Excel version 2007.

Go to Insert-Picture, and then when they ask you for the location for the file, paste "http://www.google.com.tw/images/srpr/nav_logo73.png"
and hit insert. the picture will now be inserted into the file. Save and close the file. Turn off the internet, and then reopen the file. The picture should still be there.

now try my macro from my original post
Sub Macro1()
'
    Dim SelRange As Range
    Set SelRange = Selection
    sImageName = SelRange.Value
    ActiveSheet.Pictures.Insert(sImageName).Select
    Selection.ShapeRange.Height = 80
    SelRange.Value = ""
End Sub

so for this macro, you put the link into any cell
"http://www.google.com.tw/images/srpr/nav_logo73.png"

and with the selection on the cell, you run the macro. The macro will insert the picture, and resize it to 80. Now Save and close the spreadsheet. Turn off the internet. Reopen the file, you will see the image will now turn into a box with a red X.

I hope this helps.

Thanks,
Brian
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36020655
Thanks.  This is very useful.

I'll work on it a bit and see what I can come up with.

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36023014
Well, I did that.  See attached file.  Everything looks peachy when I'm Offline (just disabled my LAN controller, then checked google refresh and got not connected on IE to verify).

Open this attached and let me know how it looks to you, offline.

Dave
testPic-r1.xls
0
 

Author Comment

by:bsun711
ID: 36044913
seems ok. did you just do the steps i listed out above? macro is the same? or did you do something different with the macro?
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36045028
I did nothing different, just followed your steps so I could see the failure and I couldn't.

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36101403
bsun - what's your status on this?

Dave
0
 

Author Comment

by:bsun711
ID: 36122291
It didn't work when I used my macro, which i pasted above. It is still giving me "the linked photo can not be displayed" Not sure how you got it to work? I'm using office 2010.
Book1.xls
Book1.xlsm
Capture.PNG
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36122338
I tried both yours and mine with Excel 2007.  I went through the same operation, pasting the link, running the macro, saving, disconnecting from the internet, etc., and it works fine.

When I tried this with Excel 2010, I cannot replicate the error, either...

What's puzzling is when I pull up BOOK1.XLS, the picture displays correctly.  The Book1.xls you posted does not for you, is this correct?  If not, please post that book, so I can see if I see it differently.

Here's a tip on how to select and save images to a file (you said that would be a handy tip to have):http://www.mrexcel.com/forum/showthread.php?t=233108

You could theoretically then run your macro to iterate through all your URLs, then save them as files, THEN insert those pictures.

This is not the ultimate solution you're looking for.  I believe there's a configuration/installation issue with the inability for you to run this macro and have it actually insert a picture object (as opposed to a link with picture representation requiring you to be online at the time of display)...

If we don't get anywhere after your next post, I'll post a request with other Experts who might have ideas.

Dave
0
 

Author Comment

by:bsun711
ID: 36122401
hmm, i couldn't display images with both files. I posted my book1.xls in my previous post..here it is again


Book1.xls
0
 

Author Comment

by:bsun711
ID: 36122402
but yea, i do agree that the problem is that the macro is insertign a link, instead of a picture object...you think it's my settings? any idea which setting it may be?
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36122422
Ok - now I see what you see.  Not sure the conditions that create it.  I'll flag this with the Experts.

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36122454
Try this:

 
Sub Macro1()
'
    Dim SelRange As Range
    Set SelRange = Selection
    sImageName = SelRange.Value
    ActiveSheet.Pictures.Insert(sImageName).Select
    Selection.Cut
    ActiveSheet.PasteSpecial Format:="Picture (JPEG)", Link:=False, DisplayAsIcon:=False
    Selection.ShapeRange.Height = 80
    SelRange.Value = ""
End Sub

Open in new window


For whatever reason, your implementation keeps the link with the insert picture.  This cuts and paste special, getting rid of the link...

Let me know if it works...

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36122462
You may have a bug in your version of 2010.  Here's a post that is almost just like yours, where the bug is cited, and alternative code using the AddPicture command as an alternative to the solution (cut/pastespecial with links = false), above.

http://social.msdn.microsoft.com/Forums/en-US/isvvba/thread/482e5387-cb6b-4941-b33a-50e232eead3a/

Dave
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 36122887
Dave if I remember correctly (I could be wrong though) you cannot insert picture like that post Excel 2007. I had replied to a similar post earlier (either here or in vbforums.com) but I don't have the time to search for it now as I am at the airport now and just got the time to reply to this post :D

You will have to insert a shape first and then insert a picture in that shape.

Sid
0
 

Author Comment

by:bsun711
ID: 36124047
Dave,

i think that office 2010 was the problem. I used your macro code and it worked!

Just one more little thing, how do I write a macro to find all the picture links in the spreadsheet, and have the macro you wrote above insert the picture object for every link?

Thanks,
Brian

0
 
LVL 42

Accepted Solution

by:
dlmille earned 1400 total points
ID: 36126315
Sure.

Here's the code:
 
Sub linksToPics()
Dim mySheet As Worksheet
Dim SelRange As Range
Dim myPict As Object
Dim myPictLink As Hyperlink

   For Each mySheet In Application.Worksheets
        mySheet.Activate
        For Each myPictLink In mySheet.Hyperlinks
            
            Set SelRange = myPictLink.Range
            sImageName = myPictLink.Address
            Set myPict = mySheet.Pictures.Insert(sImageName) '.Select is not needed
            myPict.Cut 'Selection.Cut
            mySheet.PasteSpecial Format:="Picture (JPEG)", Link:=False, DisplayAsIcon:=False
            Set myPict = Selection
            myPict.ShapeRange.Height = 80
            myPict.Top = SelRange.Top
            myPict.Left = SelRange.Left
            SelRange.Value = ""
        Next myPictLink
    Next mySheet
    
End Sub

Open in new window


See attached file you can test.  Note, I'm picking up hyperlinks in the loop, based on one of your original demo file posts.  If your "links" are text values, you'd need to do a .Find operation on http or .png or something...

Dave
testPic-r3.xls
0
 

Author Comment

by:bsun711
ID: 36193619
Dave,

So it turns out the Http are indeed text values. how do I go about doing .find operation into the loop?
Thanks

Brian
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36195224
No problem - can you ask a related question?  Other's can also help out on this.  Also, it would be a good separate tip for the knowledgebase.  Specify what you need / how you would like the macro modified, re: search for every occurrance of "http" on one or all worksheets in your workbook, etc...

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36195234
Be sure and click ASK a RELATED Question - See the link in the comment box, just below this post.

Dave
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

601 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