Inserting picture from a dynamic URL in a cell

S

sinasdf

New Member
Joined
Dec 4, 2017
Messages
37
​I've been trying out and testing different codes online but I can't get mine to consistently work.

The situation is this:

I want to be able to paste in a URL in Cell A1 so that in Cell B2, it will show the downloaded image from the link. The worksheet will be called "URL Report"

I'm having trouble understanding the exact objects and codes that can make this happen - I'm still pretty new to VBA

I've been using this code:
Code:
<code class="vb keyword" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: bold !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; white-space: nowrap; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">Sub</code> <code class="vb plain" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; white-space: nowrap; font-size: 1em !important; color: rgb(0, 0, 0) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">URLPictureInsert()</code>
<code class="vb spaces" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; color: rgb(221, 0, 85); white-space: nowrap; font-size: 1em !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;"></code><code class="vb keyword" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: bold !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; white-space: nowrap; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">Dim</code> <code class="vb plain" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; white-space: nowrap; font-size: 1em !important; color: rgb(0, 0, 0) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">Pshp </code><code class="vb keyword" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: bold !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; white-space: nowrap; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">As</code> <code class="vb plain" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; white-space: nowrap; font-size: 1em !important; color: rgb(0, 0, 0) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">Shape</code>
<code class="vb spaces" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; color: rgb(221, 0, 85); white-space: nowrap; font-size: 1em !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;"></code><code class="vb keyword" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: bold !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; white-space: nowrap; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">On</code> <code class="vb keyword" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: bold !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; white-space: nowrap; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">Error</code> <code class="vb keyword" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: bold !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; white-space: nowrap; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">Resume</code> <code class="vb keyword" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: bold !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; white-space: nowrap; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">Next</code>
<code class="vb spaces" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; color: rgb(221, 0, 85); white-space: nowrap; font-size: 1em !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;"></code><code class="vb plain" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; white-space: nowrap; font-size: 1em !important; color: rgb(0, 0, 0) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">Application.ScreenUpdating = </code><code class="vb keyword" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: bold !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; white-space: nowrap; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">False</code>
<code class="vb spaces" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; color: rgb(221, 0, 85); white-space: nowrap; font-size: 1em !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;"></code><code class="vb keyword" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: bold !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; white-space: nowrap; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">Set</code> <code class="vb plain" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; white-space: nowrap; font-size: 1em !important; color: rgb(0, 0, 0) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">Rng = Worksheets("URL Report").Range(</code><code class="vb string" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; white-space: nowrap; font-size: 1em !important; color: rgb(0, 0, 255) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">"A1"</code><code class="vb plain" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; white-space: nowrap; font-size: 1em !important; color: rgb(0, 0, 0) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">)</code>
<code class="vb spaces" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; color: rgb(221, 0, 85); white-space: nowrap; font-size: 1em !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;"></code><code class="vb keyword" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: bold !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; white-space: nowrap; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">For</code> <code class="vb keyword" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: bold !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; white-space: nowrap; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">Each</code> <code class="vb plain" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; white-space: nowrap; font-size: 1em !important; color: rgb(0, 0, 0) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">cell </code><code class="vb keyword" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: bold !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; white-space: nowrap; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">In</code> <code class="vb plain" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; white-space: nowrap; font-size: 1em !important; color: rgb(0, 0, 0) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">Rng</code>
<code class="vb spaces" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; color: rgb(221, 0, 85); white-space: nowrap; font-size: 1em !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;"></code><code class="vb plain" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; white-space: nowrap; font-size: 1em !important; color: rgb(0, 0, 0) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">filenam = cell</code>
<code class="vb spaces" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; color: rgb(221, 0, 85); white-space: nowrap; font-size: 1em !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">W</code><code class="vb plain" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; white-space: nowrap; font-size: 1em !important; color: rgb(0, 0, 0) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">orksheets("URL Report").Pictures.Insert(filenam).</code><code class="vb keyword" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: bold !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; white-space: nowrap; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">Select</code>
<code class="vb spaces" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; color: rgb(221, 0, 85); white-space: nowrap; font-size: 1em !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;"></code><code class="vb keyword" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: bold !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; white-space: nowrap; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">Set</code> <code class="vb plain" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; white-space: nowrap; font-size: 1em !important; color: rgb(0, 0, 0) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">Pshp = Selection.ShapeRange.Item(1)</code>
<code class="vb spaces" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; color: rgb(221, 0, 85); white-space: nowrap; font-size: 1em !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;"></code><code class="vb keyword" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: bold !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; white-space: nowrap; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">With</code> <code class="vb plain" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; white-space: nowrap; font-size: 1em !important; color: rgb(0, 0, 0) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">Pshp</code>
<code class="vb spaces" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; color: rgb(221, 0, 85); white-space: nowrap; font-size: 1em !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;"></code><code class="vb plain" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; white-space: nowrap; font-size: 1em !important; color: rgb(0, 0, 0) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">.LockAspectRatio = msoTrue</code>
<code class="vb spaces" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; color: rgb(221, 0, 85); white-space: nowrap; font-size: 1em !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;"></code><code class="vb plain" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; white-space: nowrap; font-size: 1em !important; color: rgb(0, 0, 0) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">.Width = 100</code>
<code class="vb spaces" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; color: rgb(221, 0, 85); white-space: nowrap; font-size: 1em !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;"></code><code class="vb plain" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; white-space: nowrap; font-size: 1em !important; color: rgb(0, 0, 0) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">.Height = 100</code>
<code class="vb spaces" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; color: rgb(221, 0, 85); white-space: nowrap; font-size: 1em !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;"></code><code class="vb plain" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; white-space: nowrap; font-size: 1em !important; color: rgb(0, 0, 0) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">.Cut</code>
<code class="vb spaces" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; color: rgb(221, 0, 85); white-space: nowrap; font-size: 1em !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;"></code><code class="vb keyword" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: bold !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; white-space: nowrap; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">End</code> <code class="vb keyword" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: bold !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; white-space: nowrap; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">With</code>
<code class="vb spaces" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; color: rgb(221, 0, 85); white-space: nowrap; font-size: 1em !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;"></code><code class="vb plain" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; white-space: nowrap; font-size: 1em !important; color: rgb(0, 0, 0) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">Cells(cell.Row, cell.Column + 1).PasteSpecial</code>
<code class="vb spaces" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; color: rgb(221, 0, 85); white-space: nowrap; font-size: 1em !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;"></code><code class="vb keyword" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: bold !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; white-space: nowrap; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">Next</code>
<code class="vb spaces" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; color: rgb(221, 0, 85); white-space: nowrap; font-size: 1em !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;"></code><code class="vb plain" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: inherit; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; white-space: nowrap; font-size: 1em !important; color: rgb(0, 0, 0) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">Application.ScreenUpdating = </code><code class="vb keyword" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: bold !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; white-space: nowrap; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">True</code>
<code class="vb keyword" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: bold !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; white-space: nowrap; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">End</code> <code class="vb keyword" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: bold !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; white-space: nowrap; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">Sub</code><code class="vb keyword" style="margin: 0px !important; padding: 0px !important; font-style: inherit; font-weight: bold !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; white-space: nowrap; font-size: 1em !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;"></code>
<code class="vb keyword" style="white-space: nowrap; margin: 0px !important; padding: 0px !important; font-weight: bold !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; line-height: 1.1em !important; font-size: 1em !important; background: none !important; color: rgb(0, 102, 153) !important; border-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; min-height: auto !important;">

</code>
I F8'd line by line but I still can't get the image to extract into the cell - any advice?
 
Sort by date Sort by votes

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Domenic said:
An alternative would be to first download the the file to a temporary folder using the XMLHTTP object, and then insert the file from there into Excel using the AddPicture method. So, as before, we would have the function URLExists to make sure the URL exists, and then we would have another function, SaveWebFile, to download the file into a temporary folder. Then, once it's inserted into the worksheet using the AddPicture method, the temporary file is deleted.

Code: 
Function URLExists(sURL As String) As Boolean

    Dim XMLReq As Object
    
    On Error GoTo ErrHandler
    
    Set XMLReq = CreateObject("MSXML2.XMLHTTP")
    With XMLReq
        .Open "GET", sURL, False
        .Send
        URLExists = (.Status = 200)
    End With
    
ErrHandler:
    Set XMLReq = Nothing
    
End Function

Code: 
'http://www.vbaexpress.com/kb/getarticle.php?kb_id=799

Function SaveWebFile(ByVal vWebFile As String, ByVal vLocalFile As String) As Boolean
    Dim oXMLHTTP As Object, i As Long, vFF As Long, oResp() As Byte
     
     'You can also set a ref. to Microsoft XML, and Dim oXMLHTTP as MSXML2.XMLHTTP
    Set oXMLHTTP = CreateObject("MSXML2.XMLHTTP")
    oXMLHTTP.Open "GET", vWebFile, False 'Open socket to get the website
    oXMLHTTP.Send 'send request
     
     'Wait for request to finish
    Do While oXMLHTTP.readyState <> 4
        DoEvents
    Loop
     
    oResp = oXMLHTTP.responseBody 'Returns the results as a byte array
     
     'Create local file and save results to it
    vFF = FreeFile
    If Dir(vLocalFile) <> "" Then Kill vLocalFile
    Open vLocalFile For Binary As [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=vFF"]#vFF[/URL] 
    Put [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=vFF"]#vFF[/URL] , , oResp
    Close [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=vFF"]#vFF[/URL] 
     
     'Clear memory
    Set oXMLHTTP = Nothing
End Function

Code: 
Sub URLPictureInsert()
    Dim ws As Worksheet
    Dim rRange As Range
    Dim rCell As Range
    Dim sURL As String
    Dim sTempFile As String
    Dim LastRow As Long
    Application.ScreenUpdating = False
    Set ws = Worksheets("URL Report")
    With ws
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set rRange = .Range("A1:A" & LastRow)
    End With
    For Each rCell In rRange
        If Len(rCell) > 0 Then
            sURL = rCell.Value
            If URLExists(sURL) Then
                sTempFile = Environ("temp") & "\" & Mid(sURL, InStrRev(sURL, "/") + 1)
                SaveWebFile sURL, sTempFile
                ws.Shapes.AddPicture _
                    Filename:=sTempFile, _
                    LinkToFile:=msoFalse, _
                    SaveWithDocument:=msoTrue, _
                    Left:=rCell.Offset(, 1).Left, _
                    Top:=rCell.Top, _
                    Width:=100, _
                    Height:=100
                    Kill sTempFile
            Else
                rCell.Offset(, 1).Value = "File not found" 'optional
            End If
        End If
    Next rCell
    Application.ScreenUpdating = True
End Sub
Click to expand...

Thanks a lot Dom - it works well!

One more thing

as I understand, the code translates all the URL links in a column.

Is it possible for me to specify to convert the image in only a specific cell, such as D6? I tried changing anything that specifies the range of cells in "A" in the code to "D6" but there are errors.

Thanks!
 
Upvote 0
In that case, try the following instead...

Code: 
[FONT=Courier New][COLOR=darkblue]Sub[/COLOR] URLPictureInsert()
    [COLOR=darkblue]Dim[/COLOR] sURL [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] sTempFile [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    sURL = Worksheets("URL Report").Range("D6").Value
    [COLOR=darkblue]If[/COLOR] Len(sURL) = 0 [COLOR=darkblue]Then[/COLOR]
        MsgBox "Cell is empty...", vbInformation
        [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] URLExists(sURL) [COLOR=darkblue]Then[/COLOR]
        MsgBox "URL does not exist...", vbInformation
        [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [COLOR=darkblue]End[/COLOR] If
    sTempFile = Environ("temp") & "\" & Mid(sURL, InStrRev(sURL, "/") + 1)
    SaveWebFile sURL, sTempFile
    ws.Shapes.AddPicture _
        Filename:=sTempFile, _
        LinkToFile:=msoFalse, _
        SaveWithDocument:=msoTrue, _
        Left:=rCell.Offset(, 1).Left, _
        Top:=rCell.Top, _
        Width:=100, _
        Height:=100
        Kill sTempFile
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR][/FONT]

Hope this helps!
 
Upvote 0
Domenic said:
In that case, try the following instead...

Code: 
[FONT=Courier New][COLOR=darkblue]Sub[/COLOR] URLPictureInsert()
    [COLOR=darkblue]Dim[/COLOR] sURL [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] sTempFile [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    sURL = Worksheets("URL Report").Range("D6").Value
    [COLOR=darkblue]If[/COLOR] Len(sURL) = 0 [COLOR=darkblue]Then[/COLOR]
        MsgBox "Cell is empty...", vbInformation
        [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] URLExists(sURL) [COLOR=darkblue]Then[/COLOR]
        MsgBox "URL does not exist...", vbInformation
        [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [COLOR=darkblue]End[/COLOR] If
    sTempFile = Environ("temp") & "\" & Mid(sURL, InStrRev(sURL, "/") + 1)
    SaveWebFile sURL, sTempFile
    ws.Shapes.AddPicture _
        Filename:=sTempFile, _
        LinkToFile:=msoFalse, _
        SaveWithDocument:=msoTrue, _
        Left:=rCell.Offset(, 1).Left, _
        Top:=rCell.Top, _
        Width:=100, _
        Height:=100
        Kill sTempFile
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR][/FONT]

Hope this helps!
Click to expand...

I put it in but I get an error after the ws.Shapes.AddPicture line with a run-time error Object required
:(
 
Upvote 0
Sorry, my mistake. Try the following instead...

Code: 
[FONT=Courier New][COLOR=darkblue]Sub[/COLOR] URLPictureInsert()
    [COLOR=darkblue]Dim[/COLOR] ws [COLOR=darkblue]As[/COLOR] Worksheet
    [COLOR=darkblue]Dim[/COLOR] rCell [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] sURL [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] sTempFile [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Set[/COLOR] ws = Worksheets("URL Report")
    [COLOR=darkblue]Set[/COLOR] rCell = ws.Range("D6")
    sURL = rCell.Value
    [COLOR=darkblue]If[/COLOR] Len(sURL) = 0 [COLOR=darkblue]Then[/COLOR]
        MsgBox "Cell is empty...", vbInformation
        [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] URLExists(sURL) [COLOR=darkblue]Then[/COLOR]
        MsgBox "URL does not exist...", vbInformation
        [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [COLOR=darkblue]End[/COLOR] If
    sTempFile = Environ("temp") & "\" & Mid(sURL, InStrRev(sURL, "/") + 1)
    SaveWebFile sURL, sTempFile
    ws.Shapes.AddPicture _
        Filename:=sTempFile, _
        LinkToFile:=msoFalse, _
        SaveWithDocument:=msoTrue, _
        Left:=rCell.Offset(, 1).Left, _
        Top:=rCell.Top, _
        Width:=100, _
        Height:=100
        Kill sTempFile
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR][/FONT]
 
Upvote 0
Thanks Domenic! It all looks good!

One more question if you don't mind!

So everything that is coded above is based on the URL that is in cell value D6.

The cell value in D6 is going to change dynamically as I input data into my spreadsheet that will do a look-up.

Is it possible to design the code so that whenever I put in a new value in D6, the image will update automatically? Right now it only works if I F8 the code in VBA.

Furthermore, if I were to input a new value in D6, it should remove the previous image and insert the new one...is this possible?
 
Upvote 0
Actually, that really changes things. In that case, you'll need to use a worksheet change event handler. You'll need to place this code in the code module for your sheet named "URL Report". So you'll need to right-click the sheet tab, select View Code, and place this code in the code module...

Code: 
[FONT=Courier New][COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Change([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)
    [COLOR=darkblue]If[/COLOR] Target.Address <> "$D$6" [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    Application.EnableEvents = [COLOR=darkblue]False[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] sURL [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    sURL = Target.Value
    [COLOR=darkblue]If[/COLOR] Len(sURL) = 0 [COLOR=darkblue]Then[/COLOR]
        Target.Offset(, 1).Value = ""
        [COLOR=darkblue]Call[/COLOR] DeletePicture(Target.Offset(, 1))
    [COLOR=darkblue]ElseIf[/COLOR] [COLOR=darkblue]Not[/COLOR] URLExists(sURL) [COLOR=darkblue]Then[/COLOR]
        Target.Offset(, 1).Value = "N/A"
        [COLOR=darkblue]Call[/COLOR] DeletePicture(Target.Offset(, 1))
    [COLOR=darkblue]Else[/COLOR]
        Target.Offset(, 1).Value = ""
        [COLOR=darkblue]Call[/COLOR] InsertPicture(sURL, Target.Offset(, 1))
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    Application.EnableEvents = [COLOR=darkblue]True[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]


[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] InsertPicture(sURL [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR], rRange [COLOR=darkblue]As[/COLOR] Range)
    [COLOR=darkblue]Dim[/COLOR] sTempFile [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    sTempFile = Environ("temp") & "\" & Mid(sURL, InStrRev(sURL, "/") + 1)
    SaveWebFile sURL, sTempFile
    Me.Shapes.AddPicture _
        Filename:=sTempFile, _
        LinkToFile:=msoFalse, _
        SaveWithDocument:=msoTrue, _
        Left:=rRange.Left, _
        Top:=rRange.Top, _
        Width:=100, _
        Height:=100
        Kill sTempFile
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]


[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] DeletePicture(rRange [COLOR=darkblue]As[/COLOR] Range)
    [COLOR=darkblue]Dim[/COLOR] oShape [COLOR=darkblue]As[/COLOR] Shape
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] oShape [COLOR=darkblue]In[/COLOR] Me.Shapes
        [COLOR=darkblue]If[/COLOR] oShape.Type = msoPicture [COLOR=darkblue]Then[/COLOR]
            [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] Intersect(oShape.TopLeftCell, rRange) [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
                oShape.Delete
                [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] oShape
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR][/FONT]

Hope this helps!
 
Upvote 0
Domenic said:
Actually, that really changes things. In that case, you'll need to use a worksheet change event handler. You'll need to place this code in the code module for your sheet named "URL Report". So you'll need to right-click the sheet tab, select View Code, and place this code in the code module...

Code: 
[FONT=Courier New][COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Change([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)
    [COLOR=darkblue]If[/COLOR] Target.Address <> "$D$6" [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    Application.EnableEvents = [COLOR=darkblue]False[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] sURL [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    sURL = Target.Value
    [COLOR=darkblue]If[/COLOR] Len(sURL) = 0 [COLOR=darkblue]Then[/COLOR]
        Target.Offset(, 1).Value = ""
        [COLOR=darkblue]Call[/COLOR] DeletePicture(Target.Offset(, 1))
    [COLOR=darkblue]ElseIf[/COLOR] [COLOR=darkblue]Not[/COLOR] URLExists(sURL) [COLOR=darkblue]Then[/COLOR]
        Target.Offset(, 1).Value = "N/A"
        [COLOR=darkblue]Call[/COLOR] DeletePicture(Target.Offset(, 1))
    [COLOR=darkblue]Else[/COLOR]
        Target.Offset(, 1).Value = ""
        [COLOR=darkblue]Call[/COLOR] InsertPicture(sURL, Target.Offset(, 1))
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    Application.EnableEvents = [COLOR=darkblue]True[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]


[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] InsertPicture(sURL [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR], rRange [COLOR=darkblue]As[/COLOR] Range)
    [COLOR=darkblue]Dim[/COLOR] sTempFile [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    sTempFile = Environ("temp") & "\" & Mid(sURL, InStrRev(sURL, "/") + 1)
    SaveWebFile sURL, sTempFile
    Me.Shapes.AddPicture _
        Filename:=sTempFile, _
        LinkToFile:=msoFalse, _
        SaveWithDocument:=msoTrue, _
        Left:=rRange.Left, _
        Top:=rRange.Top, _
        Width:=100, _
        Height:=100
        Kill sTempFile
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]


[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] DeletePicture(rRange [COLOR=darkblue]As[/COLOR] Range)
    [COLOR=darkblue]Dim[/COLOR] oShape [COLOR=darkblue]As[/COLOR] Shape
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] oShape [COLOR=darkblue]In[/COLOR] Me.Shapes
        [COLOR=darkblue]If[/COLOR] oShape.Type = msoPicture [COLOR=darkblue]Then[/COLOR]
            [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] Intersect(oShape.TopLeftCell, rRange) [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
                oShape.Delete
                [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] oShape
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR][/FONT]

Hope this helps!
Click to expand...

Hi Domenic,

I pasted that into the module and when I ran it, I got an error with a subroutine not being defined - I figured it was the URLExists function, so I put this in before the sub:

Code: 
Function URLExists(sURL As String) As Boolean

    Dim XMLReq As Object
    
    On Error GoTo ErrHandler
    
    Set XMLReq = CreateObject("MSXML2.XMLHTTP")
    With XMLReq
        .Open "GET", sURL, False
        .Send
        URLExists = (.Status = 200)
    End With
    
ErrHandler:
    Set XMLReq = Nothing
    
End Function

Now when I run it, nothing happens!
 
Upvote 0
First, make sure that you've placed the code in the correct module. Since "URL Report" contains your target cell D6, you need to place it in the code module for that sheet. So you need to right-click the sheet tab, select View Code, and paste the code in the code module.

Then, make sure that events are enabled. To check, enter the following line in the Immediate Window (Ctrl+G), and press Enter...

Code: 
? [COLOR=#333333][FONT='inherit'][I]Application.EnableEvents[/I][/FONT][/COLOR]

If it returns False, you'll need to enable events by entering the following line in the Immediate Window, and pressing Enter...

Code: 
[COLOR=#333333][FONT='inherit'][I]Application.EnableEvents = [/I][/FONT][/COLOR][COLOR=darkblue][FONT='inherit'][I]True[/I][/FONT][/COLOR]

Does this help?
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
D
  • Question Question
Get Name from Alias in Global Address List
Replies
0
Views
972
davidmg1982
D
A
  • Question Question
Importing an image into an email
Replies
0
Views
639
AllanSull
A
T
  • Question Question
Sending emails to multiple recipients with multiple PDF files via VBA & Excel
2
Replies
10
Views
10K
merxi7
M
E
  • Question Question
VBA to google search based on cell value
Replies
0
Views
2K
excelbeg
E

Forum statistics

Threads
1,224,825
Messages
6,181,191
Members
453,021
Latest member
pingpong7117

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top