Add new row to existing Excel table with XLConnect's appendWorksheet function

Question

What I'm trying to achieve is to add a row from an object in R to a table that already exists in an Excel file (Read table as in "Excel's table").

With the following code I perfectly add the row to the existing table:

library(XLConnect)
setStyleAction(wb,XLC$"STYLE_ACTION.NONE")
wb <- loadWorkbook("test.xlsx")
appendWorksheet(wb, expense, sheet="expenses", header = FALSE)
saveWorkbook(wb)

The problem here is that I´ve got tons of other formulas in Excel using the references as Expenses[@TDC] for example. If I add the row, the values will not be part of the Expenses table automatically. Is that possible to do?

Did you mean to put `wb <- ...` before the `setStyleAction`? That's the only way I can get your sample code to work — lebelinoz, Dec 14 '17 at 21:51
This is a tough one: the obvious solution ought to involve encompassing your expenses in an Excel "Table" object, and having `XLConnect` append to it. But `XLConnect` can only `readTable`; it can't `writeTable`. I have an (inelegant) solution involving VBA macros below — lebelinoz, Dec 14 '17 at 22:36

score 1 · Accepted Answer · answered Dec 14 '17 at 22:36

I suggest writing a simple formula-copying macro into your spreadsheet. You'd need to call it test.xlsm instead of test.xlsx, of course. If your spreadsheet looked like this:

then your macro might look like this:

Option Explicit

Sub MyMacro()
    Dim BottomRowOfData As Integer, BottomRowOfFormula As Integer, rowDiff As Integer

    With ThisWorkbook.Worksheets("expenses")
        BottomRowOfData = .Range("B1").End(xlDown).Row
        BottomRowOfFormula = .Range("C1").End(xlDown).Row
        rowDiff = BottomRowOfData - BottomRowOfFormula

        If (rowDiff > 0) Then
            .Range("C" & BottomRowOfFormula).Copy
            Call .Range(.Range("C" & BottomRowOfFormula + 1), .Range("C" & BottomRowOfData)).PasteSpecial
        End If
    End With

    ' Save your work
    ThisWorkbook.Save
End Sub

You can invoke VBA macros from R, as per my answer to this question on so. For completeness, here's the code from that question:

library(RDCOMClient)

# Open a specific workbook in Excel:
xlApp <- COMCreate("Excel.Application")
xlWbk <- xlApp$Workbooks()$Open("C:\\TEMP\\test.xlsm")

# this line of code is only necessary if you want to see your spreadsheet getting updated:
xlApp[['Visible']] <- TRUE 

# Run the macro called "MyMacro":
xlApp$Run("MyMacro")

# Close the workbook and quit the app:
xlWbk$Close(FALSE)
xlApp$Quit()

# Release resources:
rm(xlWbk, xlApp)
gc()

Awesome job here. It could work smoothly, but the problem is that I can't add a macro to this particular file (because it is used in devices with no macros possibilities). Obviously your answer is a totally valid way to do the job. Hope someday they add this "function" to the library as I'm sure it will be so useful for many. — Bernardo, Dec 15 '17 at 22:24
Hmm. Short of figuring out how to do more complex, macro-like Excel manipulations in R, maybe you can write some VBA macros in a "master" `.xlsm` workbook which manipulates all the other, non-macro-enabled `.xlsx` workbooks? — lebelinoz, Dec 17 '17 at 20:59

Add new row to existing Excel table with XLConnect's appendWorksheet function

1 Answers1