CODEWITHSUNDEEP
vbaexcel
IAIAIA
IAIAIA

Reputation: 93

VBA - Range to jpg picture

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

Upvotes: 9

Views: 24250

Answers (4)

iknowlab
iknowlab

Reputation: 1

My Excel VBA solution

  1. range.Select
  2. range.CopyPicture
  3. Paste it
  4. Cut it for storing to system clipboard ( key-point )
  5. create windows powershell command in one-line
  6. execute powershell by Shell
     Sub 巨集1()
        PicDir = ActiveWorkbook.Path & "\"
        PicFile = Format(Now(), "hh-mm") & ".png"
        Sheets("工作表1").Select
        Range("Q1:U10").CopyPicture Appearance:=xlScreen, Format:=xlPicture '複製範圍成圖檔
        ActiveSheet.Paste '要利用這動作-1，才會真的存到 Clipboard
        ActiveSheet.Shapes.Range(Array(Selection.ShapeRange.Name)).Select '選剛貼上的 Shape
        Selection.Cut '要利用這動作-2，才會真的存到 Clipboard
        sPSCmd = "powershell $img = get-clipboard -format image ; $img.Save('" & PicDir & PicFile & "')" '把 Clipboard 內容存成圖檔的 PowerShell
        RetVal = Shell(sPSCmd, 0) '無聲無息的執行
    End Sub

Upvotes: 0

user19256345
user19256345

Reputation: 1

I had to add

Application.Wait (Now + TimeValue("0:00:15"))

Upvotes: 0

Axel Richter
Axel Richter

Reputation: 61985

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\

Upvotes: 21

R3uK
R3uK

Reputation: 14547

Here is how to export in the same path as the workbook :

Sub Export()
Dim ws As Worksheet
Dim Rng As Range
Dim Chrt As Chart
Dim ExportPath As String

Set ws = ActiveSheet
Set Rng = ws.Range("B2:H11")
ExportPath = ThisWorkbook.Path & "\Case.jpg"

Set Chrt = ThisWorkbook.Charts.Add
Rng.CopyPicture xlScreen, xlBitmap   

With Chrt
    .Paste
    .Export FileName:=ExportPath, Filtername:="JPG"
End With
End Sub

Upvotes: 1

Related Questions