RODBC - XLSX files - dropping/clearing sheets

An embedded and charset-unspecified text was scrubbed...
Name: not available
URL: <https://stat.ethz.ch/pipermail/r-help/attachments/20090427/37e0d3bf/attachment-0001.pl>
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()
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.