xlconnect R package disables calculations in an Excel template
I'm trying to write raw data from R in an Excel template, then run a little VBA macro to turn the raw data into something nice and presentable. I use XLConnect. The problem is that XLConnect seems to disable the calculations in the Excel worksheet.
Here's my R code for writing my dataframe to Excel:
VaR.dataframe = structure(list(names = c("VaR.montecarlo", "TVaR.montecarlo", "VaR.historic.1yr", "VaR.historic.2yr", "VaR.historic.3yr", "VaR.gaussian"), X95. = c(0.0363375421892311, 0.0507403479325009, 0.0402675195649073, 0.0400483852784169, 0.0357565527744698, 0.038409381028186), X97.5. = c(0.0473956950987274, 0.0598796459552848, 0.0528350062512669, 0.0483427549702983, 0.0432047576422228, 0.0488252950503447), X99. = c(0.0589594848085542, 0.0714812674589506, 0.06597791329211, 0.0596859927016254, 0.0544507369487263, 0.0609360460943547)), .Names = c("names", "X95.", "X97.5.", "X99."), class = "data.frame", row.names = c(NA, -6L))
template_filename = "C:\\Temp\\my_template.xlsm"
writeWorksheetToFile(file = template_filename, data = VaR.dataframe, sheet = "VaR")
This goes into a raw sheet called VaR which I wouldn't show to clients. The thing I would show to clients looks more like this:
All the numbers in the table are VLOOKUP formulas into the raw VaR sheet. The final step is to run a little VBA macro which will copy-paste-special-values over the formulae, and delete the horrid-looking orange rows and columns.
The problem is after the R script has updated my Excel template, the formulas don't work. Even if I open the workbook and press F9 to calculate, they are all #N/A. This will stay true until I do a forced calculate by going Ctrl-Alt-F9.
My first instinct was to force the Ctrl-Alt-F9 key combination within my VBA macro:
Call Application.SendKeys("^%{F9}")
But this doesn't seem to work, either.
Does anyone know how to force the calculation in the macro? Or suggest an Excel-writing R package which won't corrupt my spreadsheet?
For Ctrl/Alt/F9 try Application.CalculateFull
- How to add a hover-over tooltip to rhandonstable header cell?
- How to detect the right encoding for read.csv?
- Rcpp Rf_warningcall compiler warnings
- Modify the name of factor variables in lm function(summary function)
- Extreme value analysis and quantile estimation using log Pearson type 3 (Pearson III) distribution - R vs Python
- How to hide NAs when using xlsx::saveWorkbook?
- How do I retrieve a simple numeric value from a named numeric vector in R?
- Adding an image in navbar and adjusting alignment in Rmarkdown
- Matching pair-wise columns from left to right across rows in one dataframe to another dataframe and adding new columns with matching values
- Income to outcome flow chart in Sankey plotly R
- color mapping in geom_conn_bundle not showing correctly
- Print R package startup message AFTER automatic package conflict messages instead of before
- Summing a set of R dataframe rows (column-wise), while retaining the first n columns
- Added variable / partial regression plots for groups in an interaction?
- how to make a topoplot in R with coordinates variable distribution
- List of all functions in base R?
- Plotting multiple plots for different initial conditions in one graph
- Printing repetitively on the same line in R
- Generating UI/Server based on initial selection
- Subset dataframe based on pickerInput
- How to let user pick the data in R-shiny?
- Couldn't show my simple bar charts separately on Shiny R dashboardBody
- How to programmatically filter contents of a second shiny app displayed via iframe
- How to select specific interesting groups for the boxplot in R Shiny app?
- Crosstable and Plot grouping with reactive values
- Is there a way to make multiple Shiny picker inputs where the selections must be disjoint?
- Delay/avoid duplication of shiny server side functions until after credentials
- Predictions only returns value "1"
- How to display a busy indicator in a shiny app?
- Append doesn't work when writing to CSV in R