Export to image ... way to get just cells without chart background?

S

StargateFan

I found code to export a range of cell, B1:B4, to an image on the
desktop and it works just great. I need to finetune the macro a bit
and had posted in the programming ng but there's been no answer which
only happens when the answer isn't widely known, I've found. I'm
guessing you charting folks might have a much better handle on the
issue so trying here, if I may.

Here is the macro I found:

'2011-08-17 19:27:39
(http://vbadud.blogspot.com/2010/06/how-to-save-excel-range-as-image-using.html)
Sub Export_Range_Images()
' ==========================================
' Code to save selected Excel Range as Image
' ==========================================

Dim oRange As Range
Dim oCht As Chart
Dim oImg As Picture

Set oRange = Range("B1:B4")
Set oCht = Charts.Add

oRange.CopyPicture xlScreen, xlPicture
oCht.Paste
oCht.Export Filename:="P:\0DESKTOP\TOD+TOM.gif", Filtername:="GIF"

End Sub


The process works great, it's just that I get both the cells needed,
and what looks like some sort of chart background, which is not
needed. I google xlScreen and xlPicture to see if problem lay there
but that doesn't seem to be the cause. Does anyone know how to limit
export to the cell range involved?

Thanks.
 
S

StargateFan

I found code to export a range of cell, B1:B4, to an image on the
desktop and it works just great. I need to finetune the macro a bit
and had posted in the programming ng but there's been no answer which
only happens when the answer isn't widely known, I've found. I'm
guessing you charting folks might have a much better handle on the
issue so trying here, if I may.

Here is the macro I found:

'2011-08-17 19:27:39
(http://vbadud.blogspot.com/2010/06/how-to-save-excel-range-as-image-using.html)
Sub Export_Range_Images()
' ==========================================
' Code to save selected Excel Range as Image
' ==========================================

Dim oRange As Range
Dim oCht As Chart
Dim oImg As Picture

Set oRange = Range("B1:B4")
Set oCht = Charts.Add

oRange.CopyPicture xlScreen, xlPicture
oCht.Paste
oCht.Export Filename:="P:\0DESKTOP\TOD+TOM.gif", Filtername:="GIF"

End Sub


The process works great, it's just that I get both the cells needed,
and what looks like some sort of chart background, which is not
needed. I google xlScreen and xlPicture to see if problem lay there
but that doesn't seem to be the cause. Does anyone know how to limit
export to the cell range involved?

Thanks.

Is there any way to get just the B1:B4 range of cells in picture and
omit the charting background?

Thx. :eek:D
 
S

StargateFan

I found code to export a range of cell, B1:B4, to an image on the
desktop and it works just great. I need to finetune the macro a bit
and had posted in the programming ng but there's been no answer which
only happens when the answer isn't widely known, I've found. I'm
guessing you charting folks might have a much better handle on the
issue so trying here, if I may.

Here is the macro I found:

[snip]

Working on another issue all morning, found more code to help automate
the export more. Though I'm getting a chart background still besides
B1:B4, at least the chart is now deleted automatically without user
input (oCht.Delete, along with Application.DisplayAlerts = False,
True) and without showing the chart at all (Application.ScreenUpdating
= False, True).

So less coming up on the screen which is good.





'2011-08-17 19:27:39
(http://vbadud.blogspot.com/2010/06/how-to-save-excel-range-as-image-using.html)
Sub Export_Range_Images()
' ==========================================
' Code to save selected Excel Range as Image
' ==========================================

Application.ScreenUpdating = False ' this stops new chart from
being displayed all the time

Dim oRange As Range
Dim oCht As Chart
Dim oImg As Picture

Set oRange = Range("B1:B4")
Set oCht = Charts.Add

oRange.CopyPicture xlScreen, xlPicture
oCht.Paste
oCht.Export Filename:="P:\0DESKTOP\TOD+TOM1.gif", Filtername:="GIF"
oCht.Export Filename:="P:\0DESKTOP\TOD+TOM2.gif", Filtername:="GIF"

Application.DisplayAlerts = False ' turns prompts off so that
chart is deleted automatically; no user prompt
oCht.Delete
Application.DisplayAlerts = True ' turns prompts back on

MsgBox "Images have been created. ", vbInformation '
messagebox with text and OK-button

Application.ScreenUpdating = True ' this turns screen updating
back on.
ActiveSheet.Protect ' place at end of code
End Sub
 
S

StargateFan

I found code to export a range of cell, B1:B4, to an image on the
desktop and it works just great. I need to finetune the macro a bit
and had posted in the programming ng but there's been no answer which
only happens when the answer isn't widely known, I've found. I'm
guessing you charting folks might have a much better handle on the
issue so trying here, if I may.

Here is the macro I found:

[snip]

Working on another issue all morning, found more code to help automate
the export more. Though I'm getting a chart background still besides
B1:B4, at least the chart is now deleted automatically without user
input (oCht.Delete, along with Application.DisplayAlerts = False,
True) and without showing the chart at all (Application.ScreenUpdating
= False, True).

So less coming up on the screen which is good.

Hi, everyone. I have been looking for alternative help and have gone
elsewhere but I'm no closer to fixing this than before. I sure do
miss the ngs, let me tell you! <g> If it had been this difficult in
the past, I would have given up on Excel long ago and never have had
the fantastic Excel as one of my main resources to do tasks at work
<sigh>.

I still can't get the right output size with the code below. I get
chart remnants, lines and dates. Can anyone advise how to get the
right 643 x 402 pixel size in the png output?

Thanks. I have to have hope that someone still looks at these ngs.

Cheers! :eek:D
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top