I have an Excel file where I have made a data connection (got my data from ODBC). I've also made a dataset in R which I now want to write into that existing Excel file. However, when I try to write the dataset into Excel, all of the data connections in there get lost - they're just tables, but not queries as they were before.
I have done that using openxlsx:
library(openxlsx)
wb <- loadWorkbook("myfile.xlsx")
writeData(wb,"Sheet1",mydataset,startCol=1,startRow=2)
saveWorkbook(wb,"myfile.xlsx",overwrite=T)
I have also tried excel.link:
library(excel.link)
xl.workbook.activate("myfile.xlsx")
xl.sheet.activate('Sheet1')
xl[a2] <- mydataset
and that almost works, it keeps the queries as needed but another problem appears - it ruins the encoding in that Sheet1. The special characters such as ä are now written as Ƥ.
Any ideas how I could fix the problem?
Thanks
Teele