Copy range as jpg without creating/deleting chart?

Joseph Lo

New Member
Joined
Dec 19, 2013
Messages
40
Hi guys,

I have adapted this code to my spreadsheet.

Code:
Private Sub CommandButton1_Click()


    Dim rgExp As Range: Set rgExp = Range("D6:Q27")
    ''' Copy range as picture onto Clipboard
    
    rgExp.CopyPicture Appearance:=xlScreen, Format:=xlBitmap
    ''' Create an empty chart with exact size of range copied
    
    With ActiveSheet.ChartObjects.Add(Left:=rgExp.Left, Top:=rgExp.Top, _
    Width:=rgExp.Width, Height:=rgExp.Height)
    .Name = "Table"
    .Activate
    End With
    
    ''' Paste into chart area, export to file, delete chart.
    Application.EnableEvents = False
    ActiveChart.Paste
    ActiveSheet.ChartObjects("Table").Chart.Export Filename:=Application.ActiveWorkbook.Path & "\T25(1).jpg", Filtername:="jpg"
    ActiveSheet.ChartObjects("Table").Delete
    Application.EnableEvents = True


End Sub

It works fine but only when the worksheet and workbook is not password protected. When the worksheet/workbook is protected, the sub-routine will fail.

So my question is: Is there any way I can export a saved range to a jpg via another method?

Thanks
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Search Jaafar's posts at this site for some cool API stuff for making picture files out of ws ranges (No need for all that chart stuff). HTH. Dave
 
Upvote 0
Do you have 64bit Office as well as 64bit Windows?
 
Upvote 0
That's the issue then. Those API calls need rewriting for 64bit Office. That code also uses charts anyway, so it won't really help. You'd need to adapt Stephen Bullen's PastePicture code.
 
Upvote 0
Hi

A simple solution is to add a new workbook where you create the chartobject and paste the picture. You then delete this auxiliary workbook.
 
Upvote 0
That's the issue then. Those API calls need rewriting for 64bit Office. That code also uses charts anyway, so it won't really help. You'd need to adapt Stephen Bullen's PastePicture code.

Thanks Rory. I won't pursue this method then.

Hi

A simple solution is to add a new workbook where you create the chartobject and paste the picture. You then delete this auxiliary workbook.

Thanks! I never thought of that idea before. I will explore this.
 
Upvote 0
Quick question - does it have to be a jpg? That makes life much harder without a chart.
 
Upvote 0
Well...as long as I am able to load that picture into a userform...if a BMP/GIF/PNG can do it I am all good.
 
Upvote 0

Forum statistics

Threads
1,214,659
Messages
6,120,784
Members
448,992
Latest member
prabhuk279

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