7

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
R3uK
  • 14,417
  • 7
  • 43
  • 77
IAIAIA
  • 73
  • 1
  • 1
  • 3

4 Answers4

19

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\

Axel Richter
  • 56,077
  • 6
  • 60
  • 87
  • Thanks, that got it working. What format would you recommend me to export it so I can get the best image quality since it's for a report? – IAIAIA Feb 07 '17 at 14:35
  • Hm, it depends ;-). My favorite is definitely PNG. Using `xlPicture` instead of `xlBitmap` I have provided already. But also this depends. Try different formats, compare the results and choose what is the best in your opinion. – Axel Richter Feb 07 '17 at 14:52
1

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
R3uK
  • 14,417
  • 7
  • 43
  • 77
  • Now it's generating an empty sheet in excel (Where I'm guessing the chart should show up) and a file named "False" in the directory. This is what the excel sheet looks like: http://i.imgur.com/NsqNrdN.jpg Might this be caused because of the combined cells? – IAIAIA Feb 07 '17 at 14:13
  • Great, now we're getting the picture with both the name and extension expected but it still shows blank both in excel and the .jpg :/ – IAIAIA Feb 07 '17 at 14:19
0

I had to add

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

user19256345
  • 1
0

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
iknowlab
  • 1
  • 1
  • Can you use English instead of Chinese characters ? It improves he readibility for everybody. – RobyB Mar 27 '23 at 10:19