Insert image into cell from Internet

[silkfire]

Hello, I would be greatly appreciated if someone could really help me.

I want a function that takes a URL string and then inserts a picture into the cell and fits it into the size of the cell (which I made square). The images are small but over 50 000 so it would be impractical to download them all; thus I want the spreadsheet to insert a specific one, based on my formula.

Anyone knows how? Tried a macro I found but it only gave the the '1004 Picture class' error.

So the function must be simple to use, maybe:

InsertPicture("http://mypicturedatabase/"&A2) where A2 contains the name of the picture.
<script>(function () { var ytLoop = false; var ytPlayList; var ytPLIndex; loopy = document.createElement("div"); loopy.id = "eLoopy"; a = document.createElement("label"); a.id = "eOnOff"; a.innerHTML = "Loop"; a.title = "Enable auto replay"; a.setAttribute("onClick", "LoopyOnOff(); return false;"); a.setAttribute("class", "LoopyOff"); if (window.location.href.toLowerCase().indexOf("feature=playlist") > 0) { a.innerHTML = "Loop PlayList"; urlArgs = window.location.href.slice(window.location.href.indexOf("?") + 1).split("&"); for (var i = 0; i < urlArgs.length; i++) { arg = urlArgs.split("="); if (arg[0].toLowerCase() == "p") { ytPlayList = arg[1]; } else if (arg[0].toLowerCase() == "index") { ytPLIndex = parseInt(arg[1]) + 1; } } if (ytPlayList == getCookie("LoopyPL")) { a.title = "Disable auto replay"; a.setAttribute("class", "LoopyOn"); ytLoop = true; } } loopy.appendChild(a); window.setTimeout(function () {initLoopy(true);}, 500); window.setTimeout(function () {initLoopy(false);}, 1500); window.setTimeout(function () {initLoopy(false);}, 3500); function initLoopy(addElement) { if (addElement) { document.getElementById("watch-player-div").appendChild(loopy); } ytPlayer = document.getElementById("movie_player"); ytPlayer.addEventListener("onStateChange", "onPlayerStateChange"); } onPlayerStateChange = function (newState) {if (ytLoop && newState == "0") {if (typeof ytPlayList != "undefined") {if (ytPLIndex == document.getElementById("playlistVideoCount_PL").innerHTML) {var url = document.getElementById("playlistRow_PL_0").getElementsByTagName("a")[0].href + "&playnext=1";window.setTimeout(function () {window.location = url;}, 60);}} else {window.setTimeout(function () {ytPlayer.playVideo();}, 60);}}}; LoopyOnOff = function () {if (ytLoop) {if (typeof ytPlayList != "undefined") {setCookie("LoopyPL", null);}document.getElementById("eOnOff").title = "Enable auto replay";document.getElementById("eOnOff").setAttribute("class", "LoopyOff");ytLoop = false;} else {if (typeof ytPlayList != "undefined") {setCookie("LoopyPL", ytPlayList);}document.getElementById("eOnOff").title = "Disable auto replay";document.getElementById("eOnOff").setAttribute("class", "LoopyOn");ytLoop = true;}}; function getCookie(name) { var results = document.cookie.match("(^| ?" + name + "=([^;]*)(;|$)"); if (results) { return unescape(results[2]); } else { return null; } } function setCookie(name, value) { document.cookie = name + "=" + escape(value); } if (typeof GM_addStyle == "undefined") { GM_addStyle = function (text) {var head = document.getElementsByTagName("head")[0];var style = document.createElement("style");style.setAttribute("type", "text/css");style.textContent = text;head.appendChild(style);}; } GM_addStyle("\t\t\t\t\t\t\t\t#eLoopy {\t\t\t\t\t\t\t\twidth: 28px;\t\t\t\t\t\t\tmargin-left: auto;\t\t\t\t\t\ttext-align: center;\t\t\t\t\t\tbackground: #EFEFEF;\t\t\t\t\t\tborder-left: #B1B1B1 1px solid;\t\t\t\t\tborder-right: #B1B1B1 1px solid;\t\t\t\tborder-bottom: #B1B1B1 1px solid;\t\t\t\tpadding: 1px 4px 1px 4px;\t\t\t\t\tmargin-bottom: 5px; }\t\t\t\t\t#eOnOff {\t\t\t\t\t\t\t\tfont-weight: bold;\t\t\t\t\t\ttext-decoration: none;\t\t\t \t\t\t-moz-user-select: none;\t\t\t \t\t\t-khtml-user-select: none;\t\t \t\t\tuser-select: none; }\t\t\t\t\t.LoopyOff {\t\t\t\t\t\t\t\tcolor: grey !important; }\t\t\t\t.LoopyOff:hover {\t\t\t\t\t\t\tcolor: black !important; }\t\t\t\t.LoopyOn {\t\t\t\t\t\t\t\tcolor: crimson !important; }"); })()</script>

 

[silkfire]

pgc01, could you somehow implement your code into Ron's? That would be awesome!

 

[pgc01]

pgc01, could you somehow implement your code into Ron's? That would be awesome!

Silkfire

I'm leaving. Ron's code is well structured, you should try to adapt the code. If tomorrow you still have problems please post what you've tried and what error you got and I or Ron or someone else will surely be able to help.

 

[silkfire]

Thanks, but I don't really know how to continue here...

 

[Ron Coderre]

Thanks, but I don't really know how to continue here...

Hi, silkfire

I tweaked my code with the structure that pgc01 says will work in XL2007.
Try downloading this new and...ahem...improved (hopefully) file:
http://www.savefile.com/files/2068406

Does that help?

 

[silkfire]

Thank you so much!! It works like a charm!!!

But do you know how I in the macro also change the border, because it's dark blue and 5 pix thick I think. I can change it manually so it must be doable somehow in the macro.

And also, the hyperlink that I'm creating with a formula will be concatenated from different cells, do you know how to then convert it into a hyperlink?

 

[Ron Coderre]

Thank you so much!! It works like a charm!!!

But do you know how I in the macro also change the border, because it's dark blue and 5 pix thick I think. I can change it manually so it must be doable somehow in the macro.

And also, the hyperlink that I'm creating with a formula will be concatenated from different cells, do you know how to then convert it into a hyperlink?

Ok...let's see how close to "done" we get this time.

I created another file at this location:
http://www.savefile.com/files/2068513

Here are the rules.

There's a macro in that file that converts the cells that calculate
the hyperlink location into actual hyperlinks. Once that's done,
the other program will replace those hyperlinks with pictures.

1) make sure the calculated cells only contain the hyperlink location,
not the HYPERLINK function.
Example:
A1: http://www.contextures.com/images/amazon/
B1: 21wQWbc4qKL._AA_SL160_.jpeg
C1: =A1&B1

2) Select the range of calculated locations (cell C1 in the example above)
3) ALT+F8....Select: CovertTxt2Hyperlink....click: Run
(That should convert them to actual hyperlinks)
(and Yes...I now see the typo in "Convert"...sheesh!)

4) ALT+F8....Select: ConvertHLinksToCellPics....click: Run
(That program has been changed to make the line very thin.)

Does that help?

 

[silkfire]

Thank you Ron, but do you know how to change the line color, I think it defaults to dark blue but I want it black.

 

[silkfire]

Oh nevermind my last post, I achieved it with .Line.ForeColor.RGB = RGB(0, 0, 0).

Back to the hyperlinks. Can you help me make the cells with the hyperlinks back to the original formula? And make that formula text white because I don't want to see the text underneath (I changed your code to make the images slightly smaller than the cell).
The reason for this is because I want to be able to drag the cell formula so that I can use it for the cells underneath, but your code just turns it into text, I want to keep the original formula, and in white (hidden) color.
Also, my image cells are square. But those underneath are rectangular. How do I make so when I drag+copy the formula I want the new cells to get the same size as the copied formula? I hope you understand my point. Thanks again!

 

[Ron Coderre]

I need a favor. Can you to post a shrinky-dink version of your file to one
of the free file hosting services (with any sensitive material removed, of
course) and post the link to this forum.

Please include ALL of the requirements, so we can make one last push
to get this where you want it. The "Oh, one more thing" approach is
wearing me down.

Some free filehosts that could be used:
http://www.freefilehosting.net/index.cfm
http://cjoint.com/index.php
http://www.savefile.com/index.php
http://drop.io/

 

[silkfire]

Do you have 2007 or must I convert my project first? I'm using the XLSM format.