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"