OK. By the way, I only thought that I could do what I wanted! It worked once but then it failed. When I was trying to update an existing sheet I got an error message saying that it existed and when I was trying to make a new sheet (something that worked once) I got a message saying that there was no such table!
-----Original Message----- From: Prof Brian Ripley [mailto:[EMAIL PROTECTED] Sent: Tuesday, 27 March 2007 4:44 PM To: Moshe Olshansky Cc: [EMAIL PROTECTED] Subject: Re: Updating a worksheet in Excel file using RODBC Yes, sqlDrop does not work correctly for Excel worksheet names (and there are other quirks). As I said in another message, it is on my TODO list to make this work better, but in the absence of good documentation of what the Excel ODBC driver should do and several with known bugs it is largely a matter of trial-and-error. On Tue, 27 Mar 2007, Moshe Olshansky wrote: > Dear Prof. Ripley, > >> You seem not to have tried the simplest possible option. The following >> works for me (beware of wrapped lines from mailers) >> >> chan <- odbcDriverConnect("DRIVER=Microsoft Excel Driver >> (*.xls);DBQ=C:\\bdr\\hills.xls; ReadOnly=False") >> sqlSave(chan, USArrests, "tests", fast=TRUE) # or FALSE > > You are right - I have not. > It does not work exactly as it should have but this solves my problem. > I created a very small Excel file odbc1.xls containing 3 sheets (test, Sheet2 > and Sheet3). > Below is a short R session: > >> chan <- odbcDriverConnect("DRIVER=Microsoft Excel Driver >> (*.xls);DBQ=C:\\EFGraphs\\odbc1.xls; ReadOnly=False") > >x<- c(1:6) > >x <- matrix(x,nrow=3,ncol=2) > >x <- data.frame(x) > x > X1 X2 > 1 1 4 > 2 2 5 > 3 3 6 >> sqlSave(chan, x, "test", fast=FALSE) > Error in sqlSave(chan, x, "test", fast = FALSE) : > table 'test' already exists >> sqlSave(chan, x, "tests", fast=FALSE) > > As you see I was unable to overwrite an existing sheet (an attempt to drop > this table also fails), but I was able to add a new sheet to an existing > Excel file (after this action the file contains 4 sheets - the 3 it contained > and the last sheet named tests). > This allows me to do what I wanted, i.e. manually create an Excel file with a > small VBA macro, make many copies of this file (under appropriate names), > write an appropriate data to each file and then the macro will work on the > right data (different for each file). > > Thanky you! > > Moshe. > > > > > > > > > Moshe Olshansky > > Chimaera Capital Limited > Level 4 / 349 Collins Street > Melbourne, Victoria 3000 > Phone: +613 8614 8400 > Fax: +613 8614 8410 > Email: [EMAIL PROTECTED] > > Disclaimer: This message is intended only for the personal and confidential > use of the designated recipient(s) named above. If you are not the intended > recipient of this message you are hereby notified that any review, > dissemination, distribution or copying of this message is strictly > prohibited. This communication is for information purposes only and should > not be regarded as an offer to sell or as a solicitation of an offer to buy > any financial product, an official confirmation of any transaction, or as an > official statement of Chimaera Capital Limited. E-mail transmissions cannot > be guaranteed to be secure or error-free. Therefore, we do not represent that > this information is complete or accurate and it should not be relied upon as > such. All information is subject to change without notice. > > > -- Brian D. Ripley, [EMAIL PROTECTED] Professor of Applied Statistics, http://www.stats.ox.ac.uk/~ripley/ University of Oxford, Tel: +44 1865 272861 (self) 1 South Parks Road, +44 1865 272866 (PA) Oxford OX1 3TG, UK Fax: +44 1865 272595 ______________________________________________ R-help@stat.math.ethz.ch 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.
