Home > Net >  Excel Save range as jpg
Excel Save range as jpg

Time:12-03

I have a workbook where I create printouts for a sort of to-do list. Each to-do list is the same number of cells/size. Instead of printing these lists, I want to export each list as a jpg file. I found this thread :VBA - Range to jpg picture which got me started but I am unsure on how to set the filename as a combination of 2 cells within the selected range.

Range is always 6x23 cells

Example range 1: B2:G24 Filename should be = F2 & C24 & ".jgp"

Example range 2: J27:O49 Filename should be = N27 & K49 & ".jgp"

This code exports the selected range as Case.jpg

Sub Export()

 Dim oWs As Worksheet
 Dim oRng As Range
 Dim oChrtO As ChartObject
 Dim lWidth As Long, lHeight As Long

 Set oWs = ActiveSheet
 Set oRng = Selection

 oRng.CopyPicture xlScreen, xlPicture
 lWidth = oRng.Width
 lHeight = oRng.Height

 Set oChrtO = oWs.ChartObjects.Add(Left:=0, Top:=0, Width:=lWidth, Height:=lHeight)

 oChrtO.Activate
 With oChrtO.Chart
  .Paste
  .Export Filename:="Case.jpg", Filtername:="JPG"
 End With

 oChrtO.Delete

End Sub

CodePudding user response:

If oRng is your selection then the first cell (F2 for Range1) would be oRng.Cells(1, 5) and the other (C24 for Range1) would be oRng.Cells(23, 2)

So:

.Export Filename:="C:\Test\" & oRng.Cells(1, 5).Value & "-" & _
                   oRng.Cells(23, 2).Value & ".jpg", Filtername:="JPG"
  • Related