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?
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
Try the following...

Code: 
[FONT=Courier New][FONT=Courier New][COLOR=darkblue]Sub[/COLOR] URLPictureInsert()
    [COLOR=darkblue]Dim[/COLOR] ws [COLOR=darkblue]As[/COLOR] Worksheet
    [COLOR=darkblue]Dim[/COLOR] rRange [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] rCell [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] sFileName [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] LastRow [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    Application.ScreenUpdating = [COLOR=darkblue]False[/COLOR]
    [COLOR=darkblue]Set[/COLOR] ws = Worksheets("URL Report")
    [COLOR=darkblue]With[/COLOR] ws
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        [COLOR=darkblue]Set[/COLOR] rRange = .Range("A1:A" & LastRow)
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] rCell [COLOR=darkblue]In[/COLOR] rRange
        sFileName = rCell.Value
        ws.Shapes.AddPicture _
            Filename:=sFileName, _
            LinkToFile:=msoFalse, _
            SaveWithDocument:=msoTrue, _
            Left:=rCell.Offset(, 1).Left, _
            Top:=rCell.Top, _
            Width:=100, _
            Height:=100
    [COLOR=darkblue]Next[/COLOR] rCell
    Application.ScreenUpdating = [COLOR=darkblue]True[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR][/FONT][/FONT]

You'll notice that Shapes.AddPicture is used instead of Pictures.Insert.

Hope this helps!
 
Last edited:
Upvote 0
Domenic said:
Try the following...

Code: 
[FONT=Courier New][FONT=Courier New][COLOR=darkblue]Sub[/COLOR] URLPictureInsert()
    [COLOR=darkblue]Dim[/COLOR] ws [COLOR=darkblue]As[/COLOR] Worksheet
    [COLOR=darkblue]Dim[/COLOR] rRange [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] rCell [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] sFileName [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] LastRow [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    Application.ScreenUpdating = [COLOR=darkblue]False[/COLOR]
    [COLOR=darkblue]Set[/COLOR] ws = Worksheets("URL Report")
    [COLOR=darkblue]With[/COLOR] ws
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        [COLOR=darkblue]Set[/COLOR] rRange = .Range("A1:A" & LastRow)
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] rCell [COLOR=darkblue]In[/COLOR] rRange
        sFileName = rCell.Value
        ws.Shapes.AddPicture _
            Filename:=sFileName, _
            LinkToFile:=msoFalse, _
            SaveWithDocument:=msoTrue, _
            Left:=rCell.Offset(, 1).Left, _
            Top:=rCell.Top, _
            Width:=100, _
            Height:=100
    [COLOR=darkblue]Next[/COLOR] rCell
    Application.ScreenUpdating = [COLOR=darkblue]True[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR][/FONT][/FONT]

You'll notice that Shapes.AddPicture is used instead of Pictures.Insert.

Hope this helps!
Click to expand...

Thanks Domenic

When I press F5 to go through the code, it gives me the error:

"The specified file wasn't found, and it highlights this portion:
Code: 
ws.Shapes.AddPicture _
Filename:=sFileName, _
LinkToFile:=msoFalse, _
SaveWithDocument:=msoTrue, _
Left:=rCell.Offset(, 1).Left, _
Top:=rCell.Top, _
Width:=100, _
Height:=100
 
Last edited:
Upvote 0
You can check whether the URL exists before trying to download the picture. First, place the following function in a regular module...

Code: 
[FONT=Courier New][COLOR=darkblue]Function[/COLOR] URLExists(sURL [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]) [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Boolean[/COLOR]

    [COLOR=darkblue]Dim[/COLOR] XMLReq [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Object[/COLOR]
    
    [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] LCase(sURL) [COLOR=darkblue]Like[/COLOR] "http://*" [COLOR=darkblue]Then[/COLOR]
        sURL = "http://" & sURL
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] ErrHandler
    
    [COLOR=darkblue]Set[/COLOR] XMLReq = CreateObject("MSXML2.XMLHTTP")
    [COLOR=darkblue]With[/COLOR] XMLReq
        .Open "GET", sURL, [COLOR=darkblue]False[/COLOR]
        .send
        URLExists = (.Status = 200)
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
ErrHandler:
    [COLOR=darkblue]Set[/COLOR] XMLReq = [COLOR=darkblue]Nothing[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Function[/COLOR][/FONT]

Then, make the following changes in red...

Code: 
[FONT=Courier New][COLOR=darkblue]Sub[/COLOR] URLPictureInsert()
    [COLOR=darkblue]Dim[/COLOR] ws [COLOR=darkblue]As[/COLOR] Worksheet
    [COLOR=darkblue]Dim[/COLOR] rRange [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] rCell [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] sFileName [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] LastRow [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    Application.ScreenUpdating = [COLOR=darkblue]False[/COLOR]
    [COLOR=darkblue]Set[/COLOR] ws = Worksheets("URL Report")
    [COLOR=darkblue]With[/COLOR] ws
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        [COLOR=darkblue]Set[/COLOR] rRange = .Range("A1:A" & LastRow)
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] rCell [COLOR=darkblue]In[/COLOR] rRange
        sFileName = rCell.Value
        [COLOR=#ff0000]If URLExists(sFileName) Then[/COLOR]
            ws.Shapes.AddPicture _
                Filename:=sFileName, _
                LinkToFile:=msoFalse, _
                SaveWithDocument:=msoTrue, _
                Left:=rCell.Offset(, 1).Left, _
                Top:=rCell.Top, _
                Width:=100, _
                Height:=100
        [COLOR=#ff0000]Else
            rCell.Offset(, 1).Value = "File not found" [/COLOR][COLOR=#008000]'optional[/COLOR][COLOR=#ff0000]
        End If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] rCell
    Application.ScreenUpdating = [COLOR=darkblue]True[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR][/FONT]

Hope this helps!
 
Upvote 0
Hi Domenic,

Thank you again for your help - I appreciate it

I played around with the links and I finally got it to work - I noticed however that some links work and some don't.

For example, I have used the two images:
[TABLE="width: 585"]
<tbody>[TR]
[TD]http://glintdemoz.com/timelylife/assets/attached_files/923_2016_06_11_12_23_27_test.jpg[/TD]
[/TR]
[TR]
[TD]https://images-na.ssl-images-amazon.com/images/I/41xfT8vfYnL.jpg

The first one works fine, but the second one doesn't.

I then played around with the code to use https instead of http, which results in the first one showing File not found, while the second one results in a run-time error with "the specified file wasn't found"

Do you have any ideas why the second image URL is giving me issues?[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
That's because the function URLExists checks whether the URL starts with "http://". If not, it adds it at the beginning. And so with a URL starting with "https://", the test fails. As a result, it adds "http://" at the beginning, which makes the URL incorrect. Since it looks like "http" doesn't need to be part of the URL, you can simply remove this part of the code...

Code: 
    If Not LCase(sURL) Like "http://*" Then
        sURL = "http://" & sURL
    End If
 
Upvote 0
Domenic said:
That's because the function URLExists checks whether the URL starts with "http://". If not, it adds it at the beginning. And so with a URL starting with "https://", the test fails. As a result, it adds "http://" at the beginning, which makes the URL incorrect. Since it looks like "http" doesn't need to be part of the URL, you can simply remove this part of the code...

Code: 
    If Not LCase(sURL) Like "http://*" Then
        sURL = "http://" & sURL
    End If
Click to expand...

I did remove this and its still giving me the "file wasn't found" error

It happens at this point of the code for the https link:

ws.Shapes.AddPicture _
Filename:=sFileName, _
LinkToFile:=msoFalse, _
SaveWithDocument:=msoTrue, _
Left:=rCell.Offset(, 1).Left, _
Top:=rCell.Top, _
Width:=100, _
Height:=100

I just tried it with another link with https that is not amazon related and it works fine.

But only the amazon link has the issue. For the life of me I can't figure why!
 
Upvote 0
First, let me correct myself. The "http" part is indeed needed. Even so, when I try running the code with the problem URL included, the function URLExists returns True, but I get the same runtime error when it tries to add/download the picture. From what I can tell, there doesn't seem to be any characters that need to be encoded, so I don't know why an error occurs.
 
Last edited:
Upvote 0
Domenic said:
First, let me correct myself. The "http" part is indeed needed. Even so, when I try running the code with the problem URL included, the function URLExists returns True, but I get the same runtime error when it tries to add/download the picture. From what I can tell, there doesn't seem to be any characters that need to be encoded, so I don't know why an error occurs.
Click to expand...

Thank you very much for your help - at least I know now the right code to accomplish this task.

The purpose of this task was to be able to insert the ASIN into a cell (Amazon's product identifier) which would populate all sorts of financial and sales metric data, as well as the image url based on a list I created.

But it's not working so it's all for naught :/
 
Upvote 0
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
 
Last edited:
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,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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