Excel: Copy range as jpg without creating/deleting chart?


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


This question generated 37 answers. To proceed to the answers, click here.

This thread is current as of January 05, 2014.


For more resources for Microsoft Excel