Output range as image

kendo679

New Member
Joined
Aug 26, 2016
Messages
25
I've been using the code below to output several images with a single button, it works perfectly but takes a long time.
You can see, I'm generating 32 images with this example. Are there any tricks someone would share to speed up the process?

Code:
 Private Sub Image(SourceRange As Range, FileName As String)    'Select range and save as png-file


    Dim Awb As Workbook
    Dim MyChart As Chart
    Dim objPict As Object
        
    'Remember where we are
    Set Awb = ActiveWorkbook
    Application.ScreenUpdating = False


    'Copy the range in required picture format
    SourceRange.CopyPicture Appearance:=xlPrinter, Format:=xlPicture
    ActiveSheet.PasteSpecial Format:="Picture" '"Bitmap"
    Set objPict = Selection


    'Switch to this workbook to make sure we have no protection problems
    ThisWorkbook.Activate
    
    With objPict
        .CopyPicture 1, 1 ':=1
        Set MyChart = ActiveSheet.ChartObjects.Add(1, 1, .Width, .Height).Chart
    End With


    With MyChart
        .Paste
        .Export FileName, "png"
        .Parent.Delete
    End With


    '// cleanup
    objPict.Delete
    Set RgCopy = Nothing
    Set objPict = Nothing
    ThisWorkbook.Saved = True
    Awb.Activate
    


End Sub


Sub output()


Dim TARGET As String
TARGET = "S:\Machine Shop\MACHINING\KWS\OIL PAN DISPLAY"
Application.ScreenUpdating = False


    Image Sheet14.Range("A2:AA52"), TARGET & "\5PA C1 CHARTS.png"
    Image Sheet14.Range("A53:AA103"), TARGET & "\5PA C1 CHARTS2.png"
    Image Sheet14.Range("A104:AA154"), TARGET & "\5PA C1 CHARTS3.png"
    Image Sheet14.Range("A155:AA205"), TARGET & "\5PA C1 CHARTS4.png"
    Image Sheet14.Range("A206:AA256"), TARGET & "\5PA C1 CHARTS5.png"
    Image Sheet14.Range("A257:AA307"), TARGET & "\5PA C1 CHARTS6.png"
    Image Sheet14.Range("A308:AA358"), TARGET & "\5PA C1 CHARTS7.png"
    Image Sheet14.Range("A359:AA409"), TARGET & "\5PA C1 CHARTS8.png"
    
    Image Sheet18.Range("A2:AA52"), TARGET & "\5PA C2 CHARTS.png"
    Image Sheet18.Range("A53:AA103"), TARGET & "\5PA C2 CHARTS2.png"
    Image Sheet18.Range("A104:AA154"), TARGET & "\5PA C2 CHARTS3.png"
    Image Sheet18.Range("A155:AA205"), TARGET & "\5PA C2 CHARTS4.png"
    Image Sheet18.Range("A206:AA256"), TARGET & "\5PA C2 CHARTS5.png"
    Image Sheet18.Range("A257:AA307"), TARGET & "\5PA C2 CHARTS6.png"
    Image Sheet18.Range("A308:AA358"), TARGET & "\5PA C2 CHARTS7.png"
    Image Sheet18.Range("A359:AA409"), TARGET & "\5PA C2 CHARTS8.png"
    
    Image Sheet19.Range("A2:AA52"), TARGET & "\5PA C3 CHARTS.png"
    Image Sheet19.Range("A53:AA103"), TARGET & "\5PA C3 CHARTS2.png"
    Image Sheet19.Range("A104:AA154"), TARGET & "\5PA C3 CHARTS3.png"
    Image Sheet19.Range("A155:AA205"), TARGET & "\5PA C3 CHARTS4.png"
    Image Sheet19.Range("A206:AA256"), TARGET & "\5PA C3 CHARTS5.png"
    Image Sheet19.Range("A257:AA307"), TARGET & "\5PA C3 CHARTS6.png"
    Image Sheet19.Range("A308:AA358"), TARGET & "\5PA C3 CHARTS7.png"
    Image Sheet19.Range("A359:AA409"), TARGET & "\5PA C3 CHARTS8.png"
    
    Image Sheet20.Range("A2:AA52"), TARGET & "\5PA C4 CHARTS.png"
    Image Sheet20.Range("A53:AA103"), TARGET & "\5PA C4 CHARTS2.png"
    Image Sheet20.Range("A104:AA154"), TARGET & "\5PA C4 CHARTS3.png"
    Image Sheet20.Range("A155:AA205"), TARGET & "\5PA C4 CHARTS4.png"
    Image Sheet20.Range("A206:AA256"), TARGET & "\5PA C4 CHARTS5.png"
    Image Sheet20.Range("A257:AA307"), TARGET & "\5PA C4 CHARTS6.png"
    Image Sheet20.Range("A308:AA358"), TARGET & "\5PA C4 CHARTS7.png"
    Image Sheet20.Range("A359:AA409"), TARGET & "\5PA C4 CHARTS8.png"
Application.ScreenUpdating = True
    
    MsgBox "DONE"
End Sub
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Forum statistics

Threads
1,223,912
Messages
6,175,340
Members
452,638
Latest member
Oluwabukunmi

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