1

I want to write an R script that copies the data range A5:X1000 from "WorksheetX" in "WorkbookX", and pastes values to the same range (A5:X1000) in "WorksheetY" in "WorkbookY". Both of the workbooks are in the same directory.

Is this possible?

Analyst Guy
  • 115
  • 13

2 Answers2

2

The openxlsx package is, in my opinion, the best for writing to Excel files. There are many more options for reading from Excel files (such as the readxl package.)

I can't use this package on the machine I'm on at the moment, but this should work.

# NOT TESTED
library(openxlsx)

foo <- read.xlsx("WorkbookX.xlsx", sheet = "WorksheetX", rows = 5:1000, cols = 1:24)

write.xlsx("WorkbookY.xlsx", sheet = "WorksheetY", startRow = 5, startCol = 1)
ngm
  • 2,539
  • 8
  • 18
  • As an update/follow-up, I have tried this, but get the error discussed in the following link: https://stackoverflow.com/questions/27952451/error-zipping-up-workbook-failed-when-trying-to-write-xlsx I still have not been able to successfully copy from one file to the other. – Analyst Guy Jan 30 '18 at 16:36
  • Follow up to my comment above: I used installr::install.rtools() to fix the zip issue. Specifically, I used Aniket's answer. I also had to read into the git link referenced in a similar question: https://stackoverflow.com/questions/47323832/zipping-up-workbook-failed-make-sure-tool-is-installed-or-zip-application-is-av Ultimately I had to use the following line to get this to work: 'Sys.setenv("R_ZIPCMD" = "C:/Rtools/bin/zip.exe") ## path to zip.exe' Ultimately, this solution doesn't work for me, because it erases all formatting, in addition the remainder of the workbook. – Analyst Guy Jan 30 '18 at 16:56
1

You can write to multiple sheets with the xlsx package. You just need to use a different sheetName for each data frame and you need to add append=TRUE:

library(xlsx)
write.xlsx(dataframe1, file="filename.xlsx", sheetName="sheet1")
write.xlsx(dataframe2, file="filename.xlsx", sheetName="sheet2", append=TRUE)
ASH
  • 20,759
  • 19
  • 87
  • 200