0

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
braX
  • 11,506
  • 5
  • 20
  • 33
jon
  • 31
  • 1
  • 5

1 Answers1

0

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"
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • 1
    This worked! thanks Tim! Before getting this to work I had to manually follow 8 different steps to save it as an image with the correct name. For anyone else that comes across this and does not know vba. you can set Filename:="C:\Test\" to a cell value like this Range("A1"). In cell A1 write the full path of the folder you would like to save the images. This way you will have separate folders for each sheet. – jon Dec 02 '21 at 16:52