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 = 1xlBitmap = 2xlWbk$Worksheets("deletedFields")$Range("A8:J36")$CopyPicture(xlScreen, xlBitmap)xlApp[['DisplayAlerts']] <- FALSEoCht <- xlApp[['Charts']]$Add()oCht$Paste()oCht$Export("C:\\Temp\\SavedRange.jpg", "JPG")oCht$Delete()# CLOSE WORKBOOK AND APPxlWbk$Close(FALSE)xlApp$Quit()# RELEASE RESOURCESoCht <- xlWbk <- xlApp <- NULL    rm(oCht, xlWbk, xlApp)gc()

Output (random data/chart)

You can do this with vbs. Most vbs is identical to vba so you can write out your dynamic vbs script which includes your macro as text and then call it with shell.

Here is a working example:

fileConn<-file("c:/rworking/test/test.vbs")writeLines(c("Dim xlApp, xlBook, xlSht",    "Dim filename",    "filename = \"c:\\Rworking\\test\\test_import.xlsx\"",    "Set xlApp = CreateObject(\"Excel.Application\")",    "xlApp.Visible = True",    "set xlBook = xlApp.WorkBooks.Open(filename)",    "set xlSht = xlApp.Worksheets(1)",    "set rng = xlSht.Range(\"A8:J36\")",    "rng.CopyPicture",    "Set oCht = xlApp.Charts",    "oCht.Add() ",    "Set oCht = oCht(1)",    "oCht.paste",    "oCht.Export \"C:\\rworking\\test\\Test.jpg\", \"JPG\""),     fileConn)close(fileConn)shell.exec("c:/rworking/test/test.vbs")

Hmm, not sure about posting, maybe it got redundant through Ian´s post. Its a bit more generic, but I can also remove it.

library(xlsx)OutputPicFileName <- "Chart.jpg"ScriptFileName <- "Chart.vbs"xclFileName <- "test_import.xlsx"xclRng <- "A8:J36"file <- system.file("tests", xclFileName, package = "xlsx")fileDirec <- unlist(strsplit(file, xclFileName))CreateChart <- function(fileDirec, OutputPicFileName, ScriptFileName, xclRng){  setwd(fileDirec)  filePath <- file(paste0(fileDirec, ScriptFileName))  writeLines(    c(      "Dim App, WBook, Sht, Rng, FileName, ChartObj, Chart",       paste0("FileName = \"", gsub("/", "\\\\", fileDirec), xclFileName ,"\""),       "Set App = CreateObject(\"Excel.Application\")",       "Set WBook = App.WorkBooks.Open(FileName)",       "Set Sht = App.Worksheets(1)",       paste0("Set Rng = Sht.Range(\"", xclRng,"\")"),       "Rng.CopyPicture",       "Set ChartObj = App.Charts",       "Set Chart = ChartObj.Add() ",       "Chart.paste",       paste0("Chart.Export \"", gsub("/", "\\\\", fileDirec) , OutputPicFileName ,"\", \"JPG\"")    ),     filePath  )  close(filePath)  shell.exec(ScriptFileName)}CreateChart(fileDirec, OutputPicFileName, ScriptFileName, xclRng)# Result in: fileDirec