Copy range as jpg without creating/deleting chart?

[Joseph Lo]

Hi guys,

I have adapted this code to my spreadsheet.

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

 

[NdNoviceHlp]

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

 

[Joseph Lo]

Thanks NdNoviceHlp. I searched the site and I believed you are referring to this post?

http://www.mrexcel.com/forum/excel-questions/495344-save-picture-desktop-2.html

I tried the code but on my system with Windows 8 64 bit. it throws an error saying the code is incompatible. I tried to change the text from user32 to user64 and it still would not compile.

Any ideas?

 

[RoryA]

Do you have 64bit Office as well as 64bit Windows?

 

[Joseph Lo]

Hi Rory, Yes. 64 bit office 2013 with 64bit Windows 8.

 

[RoryA]

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.

 

[pgc01]

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.

 

[Joseph Lo]

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.

 

[RoryA]

Quick question - does it have to be a jpg? That makes life much harder without a chart.

 

[Joseph Lo]

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.