How to Export an Excel Sheet Range to a Picture, from Within R

How to export an Excel sheet range to a picture, from within R

Consider having R do exactly as VBA does in your macro: making a COM interface to the Excel object library. You can do so with the RDCOMClient package, retaining nearly same code as macro in the R syntax.

library(RDCOMClient)

xlApp <- COMCreate("Excel.Application")
xlWbk <- xlApp$Workbooks()$Open("C:\\Path\\To\\test_import.xlsx")
xlScreen = 1
xlBitmap = 2

xlWbk$Worksheets("deletedFields")$Range("A8:J36")$CopyPicture(xlScreen, xlBitmap)

xlApp[['DisplayAlerts']] <- FALSE

oCht <- xlApp[['Charts']]$Add()
oCht$Paste()
oCht$Export("C:\\Temp\\SavedRange.jpg", "JPG")
oCht$Delete()

# CLOSE WORKBOOK AND APP
xlWbk$Close(FALSE)
xlApp$Quit()

# RELEASE RESOURCES
oCht <- xlWbk <- xlApp <- NULL    
rm(oCht, xlWbk, xlApp)
gc()

Output (random data/chart)

Using VBA code, how to export Excel worksheets as image in Excel 2003?

do you want to try the below code I found on the internet somewhere many moons ago and used.

It uses the Export function of the Chart object along with the CopyPicture method of the Range object.

References:

• MSDN - Export method as it applies to the Chart object. to save the clipboard as an Image

• MSDN - CopyPicture method as it applies to the Range object to copy the range as a picture

  dim sSheetName as string
  dim oRangeToCopy as range
  Dim oCht As Chart
  
  sSheetName ="Sheet1" ' worksheet to work on
  set  oRangeToCopy =Range("B2:H8") ' range to be copied
  
  Worksheets(sSheetName).Range(oRangeToCopy).CopyPicture xlScreen, xlBitmap
  set oCht =charts.add
  
  with oCht
      .paste
      .Export FileName:="C:\SavedRange.jpg", Filtername:="JPG"
  end with
  

VBA - Range to jpg picture

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\

How can I export an excel worksheet as image?

This worked for me in a WinForms project:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using ios = System.Runtime.InteropServices;

namespace ClipBoardTest
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        public void ExportRangeAsJpg()
        {
            Excel.Application xl;

            xl = (Excel.Application)ios.Marshal.GetActiveObject("Excel.Application");

            if (xl == null)
            {
                MessageBox.Show("No Excel !!");
                return;
            }

            Excel.Workbook wb = xl.ActiveWorkbook;
            Excel.Range r = wb.ActiveSheet.Range["A1:E10"];
            r.CopyPicture(Excel.XlPictureAppearance.xlScreen,
                           Excel.XlCopyPictureFormat.xlBitmap);

             if (Clipboard.GetDataObject() != null)
            {
                IDataObject data = Clipboard.GetDataObject();

                if (data.GetDataPresent(DataFormats.Bitmap))
                {
                    Image image = (Image)data.GetData(DataFormats.Bitmap, true);
                    this.pict1.Image = image;
                    image.Save(@"C:\_Stuff\test\sample.jpg",
                        System.Drawing.Imaging.ImageFormat.Jpeg);
                }
                else
                {
                    MessageBox.Show("No image in Clipboard !!");
                }
            }
            else
            {
                MessageBox.Show("Clipboard Empty !!");
            }  
        }

        private void button1_Click(object sender, EventArgs e)
        {
            ExportRangeAsJpg();
        }

    }
}

For a console app you need to see also: http://blog.another-d-mention.ro/programming/c/use-clipboard-copypaste-in-c-console-application/

Pasting an Excel range into an email as a picture

Here's a worked example, tested in Office 2010:

'Copy range of interest
Dim r As Range
Set r = Range("B2:D5")
r.Copy

'Open a new mail item
Dim outlookApp As Outlook.Application
Set outlookApp = CreateObject("Outlook.Application")
Dim outMail As Outlook.MailItem
Set outMail = outlookApp.CreateItem(olMailItem)

'Get its Word editor
outMail.Display
Dim wordDoc As Word.Document
Set wordDoc = outMail.GetInspector.WordEditor

'To paste as picture
wordDoc.Range.PasteAndFormat wdChartPicture

'To paste as a table
'wordDoc.Range.PasteExcelTable LinkedToExcel:=False, WordFormatting:=False, RTF:=False

Result:

In the code above I used early binding to have access to autocomplete; to use this code you need to set references to the Microsoft Outlook and Microsoft Word object libraries: Tools > References... > set checkmarks like this:

Alternatively, you can forget about the references and use late binding, declaring all the Outlook and Word objects As Object instead of As Outlook.Application and As Word.Document etc.

---

Apparently you're having trouble implementing the above; the range pastes as a table rather than a picture in your email message. I have no explanation for why that would happen.

An alternative is then to paste as an image in Excel, and then cut and paste that image into your e-mail:

'Copy range of interest
Dim r As Range
Set r = Range("B2:D5")
r.Copy

'Paste as picture in sheet and cut immediately
Dim p As Picture
Set p = ActiveSheet.Pictures.Paste
p.Cut

'Open a new mail item
Dim outlookApp As Outlook.Application
Set outlookApp = CreateObject("Outlook.Application")
Dim outMail As Outlook.MailItem
Set outMail = outlookApp.CreateItem(olMailItem)

'Get its Word editor
outMail.Display
Dim wordDoc As Word.Document
Set wordDoc = outMail.GetInspector.WordEditor

'Paste picture
wordDoc.Range.Paste

As pointed out by WizzleWuzzle, there is also the option of using PasteSpecial instead of PasteAndFormat or Paste...

wordDoc.Range.PasteSpecial , , , , wdPasteBitmap

... but for some reason, the resulting image doesn't render as well. See how the lower table is kind of blurry:

RangeToHTML & Image in Cell

You can achieve it by taking a screenshot(using VBA Code) of the relevant range (Has to be visible in the screen) and then save and import that image in Outlook..

This will get you started. I have added the comments so you should not have a problem understanding it. If you still do then simply ask.

Option Explicit

Sub SaveRngAsImage()
    Dim flName As String
    Dim ws As Worksheet
    Dim shp As Shape
    Dim objChart As ChartObject
    Dim chrt As Chart

    Set ws = ActiveSheet

    '~~> Change as applicable
    flName = "C:\Users\routs\Desktop\MyRng.jpg"

    '~~> Delete the above image
    If Dir(flName) <> "" Then Kill flName

    '~~> Check if what the user selected is a valid range
    If TypeName(Selection) <> "Range" Then
        MsgBox "Select a range first."
        Exit Sub
    End If

    '~~> Take a screenshot of the range
    Selection.CopyPicture xlScreen, xlBitmap
    DoEvents

    '~~> Paste the screenshot in the worksheet and assign it to
    '~~> a shape object so that we can use it's approx width and
    '~~> Height to create the chart object
    With ws
        .Paste
        DoEvents
        Set shp = .Shapes(.Shapes.Count)
        Set objChart = ActiveSheet.ChartObjects.Add(0, 0, shp.Width, shp.Height)
        Set chrt = objChart.Chart

        With chrt
            shp.Copy '~~> Copy the shape (in case the clipboard is cleared)
            .ChartArea.Select
            .Paste
            '~~> Save the image
            .Export ("C:\Users\routs\Desktop\MyRng.jpg")
        End With
        shp.Delete
        objChart.Delete
    End With

    '~~> Attaching the above image to outlook email body
    'https://stackoverflow.com/questions/44869790/embed-picture-in-outlook-mail-body-excel-vba
    Dim OutApp As Object
    Dim OutMail As Object

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    With OutMail
        .To = "SomeEmail@SomeServer.com"
        .Subject = "Attaching an image"
        .Attachments.Add flName, 1, 0
        .HtmlBody = "<html><p>Dear XYZ</p>" & _
        "<img src=""cid:MyRng.jpg"">"
        .Display
    End With
End Sub

Screenshot

---

Related Topics