save excel range to specific size image

[nikimitsy]

I am trying to save a specific worksheet range into a image..this have been able to figure out, what i need help is with the size in which i need to have the image to be (532x850)

here is the code i have been using

<code class="vb keyword" style="white-space: pre; font-size: 1em !important; line-height: 1.1em !important; border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace !important; font-weight: bold !important; direction: ltr !important; -webkit-box-shadow: none !important; box-shadow: none !important; display: inline !important; color: rgb(0, 102, 153) !important; background: none !important;">Dim</code>[FONT=Consolas] [/FONT]<code class="vb plain" style="white-space: pre; font-size: 1em !important; line-height: 1.1em !important; border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace !important; direction: ltr !important; -webkit-box-shadow: none !important; box-shadow: none !important; display: inline !important; color: black !important; background: none !important;">rng </code><code class="vb keyword" style="white-space: pre; font-size: 1em !important; line-height: 1.1em !important; border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace !important; font-weight: bold !important; direction: ltr !important; -webkit-box-shadow: none !important; box-shadow: none !important; display: inline !important; color: rgb(0, 102, 153) !important; background: none !important;">As</code>[FONT=Consolas] [/FONT]<code class="vb plain" style="white-space: pre; font-size: 1em !important; line-height: 1.1em !important; border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace !important; direction: ltr !important; -webkit-box-shadow: none !important; box-shadow: none !important; display: inline !important; color: black !important; background: none !important;">Excel.Range</code>[FONT=Consolas]<code class="vb keyword" style="border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace !important; font-weight: bold !important; font-size: 1em !important; direction: ltr !important; -webkit-box-shadow: none !important; box-shadow: none !important; display: inline !important; color: rgb(0, 102, 153) !important; background: none !important;">Set</code> <code class="vb plain" style="border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace !important; font-size: 1em !important; direction: ltr !important; -webkit-box-shadow: none !important; box-shadow: none !important; display: inline !important; color: black !important; background: none !important;">rng = Range(</code><code class="vb string" style="border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace !important; font-size: 1em !important; direction: ltr !important; -webkit-box-shadow: none !important; box-shadow: none !important; display: inline !important; color: blue !important; background: none !important;">"A1:R71"</code><code class="vb plain" style="border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace !important; font-size: 1em !important; direction: ltr !important; -webkit-box-shadow: none !important; box-shadow: none !important; display: inline !important; color: black !important; background: none !important;">)</code>[/FONT]
[FONT=Consolas] [/FONT]
[FONT=Consolas]<code class="vb keyword" style="border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace !important; font-weight: bold !important; font-size: 1em !important; direction: ltr !important; -webkit-box-shadow: none !important; box-shadow: none !important; display: inline !important; color: rgb(0, 102, 153) !important; background: none !important;">If</code> <code class="vb plain" style="border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace !important; font-size: 1em !important; direction: ltr !important; -webkit-box-shadow: none !important; box-shadow: none !important; display: inline !important; color: black !important; background: none !important;">ExportRangeToPicture(rng, </code><code class="vb string" style="border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace !important; font-size: 1em !important; direction: ltr !important; -webkit-box-shadow: none !important; box-shadow: none !important; display: inline !important; color: blue !important; background: none !important;">"C:\range.gif"</code><code class="vb plain" style="border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace !important; font-size: 1em !important; direction: ltr !important; -webkit-box-shadow: none !important; box-shadow: none !important; display: inline !important; color: black !important; background: none !important;">) </code><code class="vb keyword" style="border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace !important; font-weight: bold !important; font-size: 1em !important; direction: ltr !important; -webkit-box-shadow: none !important; box-shadow: none !important; display: inline !important; color: rgb(0, 102, 153) !important; background: none !important;">Then</code>[/FONT]
[FONT=Consolas]<code class="vb spaces" style="border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace !important; font-size: 1em !important; direction: ltr !important; -webkit-box-shadow: none !important; box-shadow: none !important; display: inline !important; background: none !important;">  </code><code class="vb plain" style="border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace !important; font-size: 1em !important; direction: ltr !important; -webkit-box-shadow: none !important; box-shadow: none !important; display: inline !important; color: black !important; background: none !important;">MsgBox </code><code class="vb string" style="border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace !important; font-size: 1em !important; direction: ltr !important; -webkit-box-shadow: none !important; box-shadow: none !important; display: inline !important; color: blue !important; background: none !important;">"ok!"</code>[/FONT]
[FONT=Consolas]<code class="vb keyword" style="border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace !important; font-weight: bold !important; font-size: 1em !important; direction: ltr !important; -webkit-box-shadow: none !important; box-shadow: none !important; display: inline !important; color: rgb(0, 102, 153) !important; background: none !important;">Else</code>[/FONT]
[FONT=Consolas]<code class="vb spaces" style="border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace !important; font-size: 1em !important; direction: ltr !important; -webkit-box-shadow: none !important; box-shadow: none !important; display: inline !important; background: none !important;">  </code><code class="vb plain" style="border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace !important; font-size: 1em !important; direction: ltr !important; -webkit-box-shadow: none !important; box-shadow: none !important; display: inline !important; color: black !important; background: none !important;">MsgBox </code><code class="vb string" style="border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace !important; font-size: 1em !important; direction: ltr !important; -webkit-box-shadow: none !important; box-shadow: none !important; display: inline !important; color: blue !important; background: none !important;">"Didn't work"</code>[/FONT]
<code class="vb keyword" style="white-space: pre; font-size: 1em !important; line-height: 1.1em !important; border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace !important; font-weight: bold !important; direction: ltr !important; -webkit-box-shadow: none !important; box-shadow: none !important; display: inline !important; color: rgb(0, 102, 153) !important; background: none !important;">End</code>[FONT=Consolas] [/FONT]<code class="vb keyword" style="white-space: pre; font-size: 1em !important; line-height: 1.1em !important; border-top-left-radius: 0px !important; border-top-right-radius: 0px !important; border-bottom-right-radius: 0px !important; border-bottom-left-radius: 0px !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace !important; font-weight: bold !important; direction: ltr !important; -webkit-box-shadow: none !important; box-shadow: none !important; display: inline !important; color: rgb(0, 102, 153) !important; background: none !important;">If</code>

thanks in advance

 

[nikimitsy]

bump, any insight please

 

[nikimitsy]

bump, any insight please

Please, have not been able to find a solution
Thanks in advance

 

[John_w]

It helps if you provide <code>or link to the ExportRangeToPicture code.

http://www.jpsoftwaretech.com/export-excel-range-to-a-picture-file-redux/

</code><code class="vb keyword">Function</code> <code class="vb plain">ExportRangeToPicture(rng </code><code class="vb keyword">As</code> <code class="vb plain">Excel.Range, img </code><code class="vb keyword">As</code> <code class="vb keyword">String</code><code class="vb plain">) </code><code class="vb keyword">As</code> <code class="vb keyword">Boolean</code>
<code class="vb comments">' save a range from Excel as a picture</code>
<code class="vb comments">' rng = Range to export</code>
<code class="vb comments">' img = filename & path</code>
 
<code class="vb comments">' basic error checking</code>
<code class="vb comments">' check for valid filetypes</code>
<code class="vb comments">' from http://peltiertech.com/WordPress/export-chart-as-image-file/</code>
<code class="vb keyword">Const</code> <code class="vb plain">FILE_EXT </code><code class="vb keyword">As</code> <code class="vb keyword">String</code> <code class="vb plain">= </code><code class="vb string">"gif,png,jpg,jpe,jpeg"</code>
<code class="vb keyword">If</code> <code class="vb plain">InStr(FILE_EXT, LCase$(Right$(img, 3))) = 0 </code><code class="vb keyword">Then</code>
<code class="vb spaces">  </code><code class="vb keyword">GoTo</code> <code class="vb plain">ExitProc</code>
<code class="vb keyword">End</code> <code class="vb keyword">If</code>
 
<code class="vb comments">' check for valid path</code>
<code class="vb keyword">Dim</code> <code class="vb plain">path </code><code class="vb keyword">As</code> <code class="vb keyword">String</code>
<code class="vb plain">path = Left$(img, InStrRev(img, "\"))</code>
<code class="vb keyword">If</code> <code class="vb plain">Dir(path, vbDirectory) = </code><code class="vb string">""</code> <code class="vb keyword">Then</code> <code class="vb keyword">GoTo</code> <code class="vb plain">ExitProc</code>
 
<code class="vb comments">' check for valid range</code>
<code class="vb keyword">Dim</code> <code class="vb plain">rRng </code><code class="vb keyword">As</code> <code class="vb plain">Excel.Range</code>
<code class="vb keyword">On</code> <code class="vb keyword">Error</code> <code class="vb keyword">Resume</code> <code class="vb keyword">Next</code>
<code class="vb keyword">Set</code> <code class="vb plain">rRng = rng.CurrentRegion</code>
<code class="vb keyword">On</code> <code class="vb keyword">Error</code> <code class="vb keyword">GoTo</code> <code class="vb plain">0</code>
<code class="vb keyword">If</code> <code class="vb plain">rRng </code><code class="vb keyword">Is</code> <code class="vb keyword">Nothing</code> <code class="vb keyword">Then</code> <code class="vb keyword">GoTo</code> <code class="vb plain">ExitProc</code>
 
<code class="vb comments">' check for protected worksheet</code>
<code class="vb keyword">If</code> <code class="vb plain">ActiveSheet.ProtectContents </code><code class="vb keyword">Then</code> <code class="vb keyword">GoTo</code> <code class="vb plain">ExitProc</code>
 
<code class="vb comments">' copy range to picture, put into chart, export it</code>
<code class="vb plain">Application.ScreenUpdating = </code><code class="vb keyword">False</code>
<code class="vb plain">rRng.CopyPicture xlScreen, xlPicture</code>
 
<code class="vb keyword">Dim</code> <code class="vb plain">cht </code><code class="vb keyword">As</code> <code class="vb plain">Excel.ChartObject</code>
<code class="vb keyword">Set</code> <code class="vb plain">cht = ActiveSheet.ChartObjects.Add(0, 0, rng.Width + 10, rng.Height + 10)</code>
 
<code class="vb keyword">With</code> <code class="vb plain">cht</code>
<code class="vb spaces">  </code><code class="vb plain">.Chart.Paste</code>
<code class="vb spaces">  </code><code class="vb plain">.Chart.Export img</code>
<code class="vb spaces">  </code><code class="vb plain">.Delete</code>
<code class="vb keyword">End</code> <code class="vb keyword">With</code>
 
<code class="vb comments">' if we got this far, assume success</code>
<code class="vb plain">ExportRangeToPicture = </code><code class="vb keyword">True</code>
 
<code class="vb plain">ExitProc:</code>
<code class="vb plain">Application.ScreenUpdating = </code><code class="vb keyword">True</code>
<code class="vb keyword">Set</code> <code class="vb plain">cht = </code><code class="vb keyword">Nothing</code>
<code class="vb keyword">Set</code> <code class="vb plain">rRng = </code><code class="vb keyword">Nothing</code>
<code class="vb keyword">End</code> <code class="vb keyword">Function

</code>
Change the With cht ... End With to:

Const PixelsToPoints As Single = 72 / 96
<code class="vb keyword">With</code> <code class="vb plain">cht</code>
<code class="vb spaces">  </code><code class="vb plain">.Chart.Paste</code>
<code class="vb spaces">  .Width = 532 * PixelsToPoints
  .Height = 850 * PixelsToPoints
  </code><code class="vb plain">.Chart.Export img</code>
<code class="vb spaces">  </code><code class="vb plain">.Delete</code>
<code class="vb keyword">End</code> <code class="vb keyword">With

</code>
Of course, you could further modify the function to accept the width and height (in pixels) as function parameters, to keep the function generic rather than hard-coding the values as above.

The ChartObject's Width and Height properties are in points, not pixels. The required pixels size are converted to points using the conversion shown: 72 points per inch and 96 dots per inch (DPI). 96 is the usual DPI setting of the Windows display, though it might be more, e.g. 120.

 

[nikimitsy]

so just trying to figure out if i put this in a sheet or module and how do i call the function to run

 

[John_w]

Put the code in a module and call it from your own code, e.g. a button click. Test it using something like:

Sub Test()
    Dim rng As Range
    Set rng = Range("A1:R71")

    If ExportRangeToPicture(rng, "C:\range.gif") Then
      MsgBox "ok!"
    Else
      MsgBox "Didn't work"
    EndIf
End Sub