R help - Apr 2009 - RODBC - XLSX files - dropping/clearing sheets

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]]

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.
>
>