[R] RODBC - XLSX files - dropping/clearing sheets

Gabor Grothendieck ggrothendieck at gmail.com
Mon Apr 27 02:46:19 CEST 2009


Try using RDCOMClient or rcom:

	library(RDCOMClient)
	xl <- COMCreate("Excel.Application")

	# next line optional
	xl[["Visible"]] <- TRUE
	
	wb <- xl[["Workbooks"]]$Open("/mydir/sample.xlsx")
	sheet <- wb$Sheets("Sheet2")
	xl[["DisplayAlerts"]] <- FALSE
	sheet$Delete()
	xl[["DisplayAlerts"]] <- TRUE
	xl$Save()
	xl$Quit()




On Sun, Apr 26, 2009 at 8:06 PM, Daniel Bradley <dannyboy477 at gmail.com> wrote:
> Hi!
>
> I'm manipulating XLSX data using RODBC, however a limitation which appears
> to be driver based is that you can't clear or drop sheets from the XLSX
> files, as per the following example:
>
>> library(RODBC)
>> xlsx<-odbcDriverConnect("DRIVER=Microsoft Excel Driver (*.xls, *.xlsx,
> *.xlsm, *.xlsb);DBQ=c:\\documents and settings\\desktop\\testxlsx.xlsx;
> ReadOnly=False")
>> sqlClear(xlsx,"newsheet2",errors=TRUE)
> [1] "[RODBC] ERROR: Could not
> SQLExecDirect"
>
> [2] "HY000?Юкяя\003 -5410 [Microsoft][ODBC Excel Driver] Deleting data in a
> linked table is not supported by this ISAM."
>> sqlClear(xlsx,"newsheet2",errors=TRUE)
> [1] "[RODBC] ERROR: Could not
> SQLExecDirect"
>
> [2] "HY000?Юкяя\003 -5410 [Microsoft][ODBC Excel Driver] Deleting data in a
> linked table is not supported by this ISAM."
>
> I'm wondering if anyone has or knows of a work around for this beyond
> converting the sheets to CSV files.  For context, I'm trying to update data
> on about 20 spreadsheets as a daily event, pulling data from MySql,
> formatting it, then overwriting the existing data on the spreadsheets.  This
> is the last piece of the puzzle.  Until the next puzzle.
>
>
> Thanks!
> Dan
>
>        [[alternative HTML version deleted]]
>
>
> ______________________________________________
> R-help at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>
>




More information about the R-help mailing list