Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

VBA - Range to jpg picture

Tags:

excel

vba

I'm trying to get a jpg file from a specific range in excel

I'm currently getting the

1004 Runtime error on Range method from _Worksheet object.

This is what my code looks like:

Sub Export()

Dim ws As Worksheet
Dim Rng As Range
Dim Chrt As Chart

Set ws = ActiveSheet
Set Rng = Range("B2:H11")

ws.Range(Rng).CopyPicture
Set Chrt = Charts.Add

With Chrt
    .Paste
    .Export FileName = "Case.jpg", Filtername:="JPG"
End With

End Sub
like image 221
IAIAIA Avatar asked Feb 07 '17 13:02

IAIAIA


People also ask

How do you use dictionaries in VBA?

To use the Dictionary you need to first add the reference. Select Tools->References from the Visual Basic menu. Find Microsoft Scripting Runtime in the list and place a check in the box beside it.


1 Answers

The main error has @J_Lard mentioned already in his comment.

But I would use ChartObject rather than a Chart sheet. Whith this you can determine the size of the output instead of getting the whole chart area in the picture.

And while using F8 step the paste and export will work, while real time run, the ChartObject needs to be activated.

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 = oWs.Range("B2:H11")

 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

If path is not specified, the Case.jpg will be saved in default save location. This is probably your user documents directory C:\Users\YourName\Documents\

like image 194
Axel Richter Avatar answered Oct 12 '22 13:10

Axel Richter