Help with resizing chart to image

DataBlake

Well-known Member
Joined
Jan 26, 2015
Messages
781
Office Version
  1. 2016
Platform
  1. Windows
I don't know why i cant wrap my head around this. So the objective is to have a user select a range
loop through that selection and take every row and export it as an image

VBA Code:
Sub ExportRanges()
Dim x As Long, i As Long
Dim ary1 As Variant
Dim cht As Object
Dim mypictures As String


Set rNg = Application.Selection
ary1 = Selection.Value


For i = 1 To UBound(ary1)
    mypictures = Selection.Cells(i, 1).Value & ".jpg"
    Selection.Range(Cells(i, 1), Cells(i, Selection.Columns.Count)).CopyPicture Appearance:=xlScreen, Format:=xlPicture
    ActiveSheet.Paste
    
Next i
End Sub

this is what i have so far where it will paste a picture of the row as intended, but making a chart and exporting it is alluding me.
export with the name generated from "mypictures"
now every example i see of this they create a chart using chart.add, but i don't see where they are defining the image name or how to interact with the image.
halp.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Here's a general outline if U have your data in Sheet2 columns A & B. HTH. Dave
Code:
With Sheets("Sheet2")
    LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
End With
Set X1Value = Sheets("Sheet2").Cells(1, 1)
Set Y1Value = Sheets("Sheet2").Cells(LastRow, 2)
Set ChartRange = Sheets("Sheet2").Range(X1Value, Y1Value)
Charts.Add.Location Where:=xlLocationAsObject, Name:="Sheet2"
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SetSourceData Source:=ChartRange, PlotBy:=xlColumns
Fname = ThisWorkbook.Path & "\" & "ChartSomeThing.gif"
ActiveChart.Export Filename:=Fname, Filtername:="GIF"
 
Upvote 0
i've tried adapting your code to mine, but i still have the same questions

VBA Code:
Sub ExportRanges()
Dim x As Long, i As Long
Dim ary1 As Variant
Dim cht As Object
Dim mypictures As String
Dim rng As Range

Set rng = Application.Selection
ary1 = Selection.Value


For i = 1 To UBound(ary1)
    mypictures = Selection.Cells(i, 1).Value & ".jpg"
    Selection.Range(Cells(i, 1), Cells(i, Selection.Columns.Count)).CopyPicture Appearance:=xlScreen, Format:=xlPicture
    Set rng = Selection.Range(Cells(i, 1), Cells(i, Selection.Columns.Count))
    ActiveSheet.Paste
    Charts.Add.Location Where:=xlLocationAsObject, Name:="Sheet1"
    ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
    ActiveChart.SetSourceData Source:=rng, PlotBy:=xlColumns
    ActiveChart.Export Filename:="C:\Users\bsnyder\Downloads\Turn14\1552\New folder\" & mypictures, Filtername:="JPG"
Next i
End Sub

not sure how to get my image inside the chart
not sure how to make the chart the size of the image

!!!.png


the image (row 2) is what im trying to export with the name "7"
the name part works but the chart that is exported is the chart in the bottom right.
 
Upvote 0
i now have the image inside the chart by adding this code in the loop

VBA Code:
    With ActiveChart
                 .ChartArea.Select
                 .Paste
    End With

!!!.png


now i just need to resize the chart to be JUST the image
also concerned the chart itself will be visible? im assuming i have to change the chart type from the example provided by NdNoviceHelp, but to what?
 
Upvote 0
Seems like I didn't understand...the previous code just generates a chart of charted values and saves it as a GIF. Do U just want to make an image file of any specified XL range? Do also U also want to display it somewhere? Dave
 
Upvote 0
Seems like I didn't understand...the previous code just generates a chart of charted values and saves it as a GIF. Do U just want to make an image file of any specified XL range? Do also U also want to display it somewhere? Dave

not just a selection, but every row of a selection as a separate image. I don't really care if it displays so long as its saved to the folder.
 
Upvote 0
and to my understanding based on this article

i need to resize the chart to the dimensions of the image (row) using
VBA Code:
 With ActiveChart
    .Height = ???
    .Width = ???
end with

unsure of how this works without dimensions in pixels
would x and y coordinates with ranges work here?
 
Upvote 0
Try using the following XLObjectToJPG function which should save any excel selectable object (such as ranges, shapes, charts ...) as a JPEG file:


In a Standard Module:
Code:
Option Explicit

Private Type GUID
   Data1 As Long
   Data2 As Integer
   Data3 As Integer
   Data4(0 To 7) As Byte
End Type

Private Type GdiplusStartupInput
   GdiplusVersion As Long
    #If VBA7 Then
        DebugEventCallback As LongPtr
        SuppressBackgroundThread As LongPtr
   #Else
        DebugEventCallback As Long
        SuppressBackgroundThread As Long
   #End If
   SuppressExternalCodecs As Long
End Type

Private Type EncoderParameter
   GUID As GUID
   NumberOfValues As Long
   Type As Long
   #If VBA7 Then
        Value As LongPtr
   #Else
        Value As Long
   #End If
End Type

Private Type EncoderParameters
   Count As Long
   Parameter As EncoderParameter
End Type


#If VBA7 Then
    Private Declare PtrSafe Function CopyImage Lib "user32" (ByVal handle As LongPtr, ByVal un1 As Long, ByVal n1 As Long, ByVal n2 As Long, ByVal un2 As Long) As LongPtr
    Private Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As LongPtr) As Long
    Private Declare PtrSafe Function CloseClipboard Lib "user32" () As Long
    Private Declare PtrSafe Function EmptyClipboard Lib "user32" () As Long
    Declare PtrSafe Function IsClipboardFormatAvailable Lib "user32" (ByVal wFormat As Long) As Long
    Private Declare PtrSafe Function GetClipboardData Lib "user32" (ByVal wFormat As Long) As LongPtr
    'GDI+ APIS.
    Private Declare PtrSafe Function GdiplusStartup Lib "GDIPlus" (token As LongPtr, inputbuf As GdiplusStartupInput, Optional ByVal outputbuf As Long = 0) As Long
    Private Declare PtrSafe Function GdiplusShutdown Lib "GDIPlus" (ByVal token As LongPtr) As Long
    Private Declare PtrSafe Function GdipCreateBitmapFromHBITMAP Lib "GDIPlus" (ByVal hbm As LongPtr, ByVal hPal As LongPtr, bitmap As LongPtr) As Long
    Private Declare PtrSafe Function GdipDisposeImage Lib "GDIPlus" (ByVal Image As LongPtr) As LongPtr
    Private Declare PtrSafe Function GdipSaveImageToFile Lib "GDIPlus" (ByVal Image As LongPtr, ByVal FILENAME As LongPtr, clsidEncoder As GUID, encoderParams As Any) As Long
    Private Declare PtrSafe Function CLSIDFromString Lib "ole32" (ByVal str As LongPtr, id As GUID) As Long
#Else
    Private Declare Function CopyImage Lib "user32" (ByVal handle As Long, ByVal un1 As Long, ByVal n1 As Long, ByVal n2 As Long, ByVal un2 As Long) As Long
    Private Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
    Private Declare Function CloseClipboard Lib "user32" () As Long
    Private Declare Function EmptyClipboard Lib "user32" () As Long
    Declare PtrSafe IsClipboardFormatAvailable Lib "user32" (ByVal wFormat As Long) As Long
    Private Declare Function GetClipboardData Lib "user32" (ByVal wFormat As Long) As Long
    'GDI+ APIS.
    Private Declare Function GdiplusStartup Lib "GDIPlus" (token As Long, inputbuf As GdiplusStartupInput, Optional ByVal outputbuf As Long = 0) As Long
    Private Declare Function GdiplusShutdown Lib "GDIPlus" (ByVal token As Long) As Long
    Private Declare Function GdipCreateBitmapFromHBITMAP Lib "GDIPlus" (ByVal hbm As Long, ByVal hPal As Long, bitmap As Long) As Long
    Private Declare Function GdipDisposeImage Lib "GDIPlus" (ByVal Image As Long) As Long
    Private Declare Function GdipSaveImageToFile Lib "GDIPlus" (ByVal Image As Long, ByVal FILENAME As Long, clsidEncoder As GUID, encoderParams As Any) As Long
    Private Declare Function CLSIDFromString Lib "ole32" (ByVal str As Long, id As GUID) As Long
#End If




Public Function XLObjectToJPG(ByVal Obj As Object, ByVal FILEPATH As String, ByVal FILENAME As String) As Boolean

    #If VBA7 Then
        Dim hPtr As LongPtr
    #Else
        Dim hPtr As Long
    #End If

    Const IMAGE_BITMAP = 0
    Const LR_COPYRETURNORG = &H4
    Const CF_BITMAP = 2

    On Error GoTo errHandler
   
    Obj.CopyPicture Appearance:=xlScreen, Format:=xlBitmap
    OpenClipboard 0
    If IsClipboardFormatAvailable(CF_BITMAP) Then
        hPtr = GetClipboardData(CF_BITMAP)
        hPtr = CopyImage(hPtr, IMAGE_BITMAP, 0, 0, LR_COPYRETURNORG)
        With CreateObject("VBScript.RegExp")
            .Global = True
            .IgnoreCase = True
            .Pattern = "[\x00-\x1F""<>\|:\*\?\\/]"
            FILENAME = .Replace(FILENAME, "")
            .Pattern = ".jpg$|.jpeg$"
            If .Test(FILENAME) And Len(Dir(FILEPATH & Application.PathSeparator, vbDirectory)) Then
                XLObjectToJPG = PicHandleToJPGFile(hPtr, FILEPATH & Application.PathSeparator, FILENAME)
            Else
                MsgBox "Wrong File Path or File Extension.", vbCritical, "Error!"
            End If
        End With
    End If
   
errHandler:
    EmptyClipboard
    CloseClipboard
   
     If Err Then
      MsgBox "Error: " & Err & vbNewLine & Err.Description, vbCritical, "Error!"
   End If

End Function



#If VBA7 Then
Private Function PicHandleToJPGFile(ByVal PicHandle As LongPtr, ByVal FILEPATH As String, ByVal FILENAME As String, Optional ByVal Quality As Byte = 100) As Boolean
    Dim lGDIP As LongPtr, lBitmap As LongPtr
#Else
Private Function PicHandleToJPGFile(ByVal PicHandle As Long, ByVal FILEPATH As String, ByVal FILENAME As String, Optional ByVal Quality As Byte = 100) As Boolean
    Dim lGDIP As Long, lBitmap As Long
#End If

    Dim tSI As GdiplusStartupInput, lRes As Long
    Dim tJpgEncoder As GUID, tParams As EncoderParameters

   tSI.GdiplusVersion = 1
   lRes = GdiplusStartup(lGDIP, tSI)

   If lRes = 0 Then
      lRes = GdipCreateBitmapFromHBITMAP(PicHandle, 0, lBitmap)
      If lRes = 0 Then
         CLSIDFromString StrPtr("{557CF401-1A04-11D3-9A73-0000F81EF32E}"), tJpgEncoder
         tParams.Count = 1
       
        With tParams.Parameter
            CLSIDFromString StrPtr("{1D5BE4B5-FA4A-452D-9CDD-5DB35105E7EB}"), .GUID
            .NumberOfValues = 1
            .Type = 4
            #If VBA7 Then
              .Value = VarPtr(Quality)
            #Else
              .Value = CLng(VarPtr(Quality))
            #End If
         End With
       
         lRes = GdipSaveImageToFile(lBitmap, StrPtr(FILEPATH & FILENAME), tJpgEncoder, tParams)
         GdipDisposeImage lBitmap
      End If
      GdiplusShutdown lGDIP
   End If
 
   If lRes Then
      MsgBox "Cannot save the image. GDI+ Error:", vbCritical, "Error!"
      Else
     
      PicHandleToJPGFile = True
     
   End If
 
End Function



Code Usage (applied to each row in the current range selection)
Code:
Sub Test()

    Dim oArea As Range, i As Long
   
    For Each oArea In Selection.Areas
        For i = 1 To oArea.Rows.Count
       
            Call XLObjectToJPG( _
                Obj:=oArea.Rows(i), _
                FILEPATH:="C:\Users\bsnyder\Downloads\Turn14\1552\New folder\", _
                FILENAME:=oArea.Rows(i).Cells(1) & ".jpg")
               
        Next i
    Next oArea

End Sub
 
Upvote 0
Try using the following XLObjectToJPG function which should save any excel selectable object (such as ranges, shapes, charts ...) as a JPEG file:

i don't understand 80% of what is going on here, but i love it.
works 100% to what i was asking for and is quick as can be.
i had no idea a function like this existed so i've been trying to master charts, thank you for this.
 
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

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