R help - Jul 2009 - R-help Digest, Vol 77, Issue 9

Hi,

This may be due to several reasons. That I can think about:

1) Ensure you close *all* possibly open workbooks:

	nBooks <- xl[["Workbooks"]]$Count();
	for (i in seq_len(nBooks))
		xl[["Workbooks"]]$item(i)$Close(SaveChanges=FALSE);

2) The excel application reference does not seem to be really released until the
garbage collector runs. So this may help:

	xl$Quit();
	xl <- NULL;	
	gc();

3) Ensure you don't have any "hanging" reference to the excel
application (sometimes from previous runs of your code that failed). If your
code is inside a function, wrap the code in point 1 and 2 on an on.exit() call
to ensure that excel is properly closed each time.


Hope this helps.

Enrique
	
------------------------------

Date: Wed, 8 Jul 2009 16:06:57 +0300
From: Lauri Nikkinen <lauri.nikkinen at iki.fi>
Subject: [R] RDCOMClient: how to close Excel process?
To: r-help at stat.math.ethz.ch
Message-ID:
	<ba8c09910907080606n29b8a537uac5ba1788376ff42 at mail.gmail.com>
Content-Type: text/plain; charset=windows-1252

Hi,

I?m using R package RDCOMClient (http://www.omegahat.org/RDCOMClient/)
to retrieve data from MS Excel workbook. I?m using the code below to
count the number of sheets in the workbook and then loop the data from
sheets in to a list.

############# R code ###################
library(gdata)
library(RDCOMClient)

xl <- COMCreate("Excel.Application")
sh <-
xl$Workbooks()$Open(normalizePath("sample_file.xls"))$Sheets()$Count()

DF.list <- list()
for (i in 1:sh) {
   DF.list[[i]] <- read.xls("sample_file.xls", sheet=i,
stringsAsFactors = FALSE)
   }
######################################

COMCreate opens Excel process and it can be seen from Windows Task
Manager. When I try to open sample_file.xls in Excel, it just flashes
in the screen and shuts down. When I kill (via task manager) the Excel
process COMCreate started, sample_file.xls will open normally.

The question is, how can I close the Excel process COMCreate started.
xl$Close() doesn?t seem to work. The same problem have been presented
in this post to R-help:
http://tolstoy.newcastle.edu.au/R/help/06/04/25990.html

-L

Thanks, this worked!!!

nBooks <- xl[["Workbooks"]]$Count();
for (i in seq_len(nBooks))
        xl[["Workbooks"]]$item(i)$Close(SaveChanges=FALSE);

-L

2009/7/9 Bengoechea Bartolom? Enrique (SIES 73)
<enrique.bengoechea at credit-suisse.com>:
> Hi,
>
> This may be due to several reasons. That I can think about:
>
> 1) Ensure you close *all* possibly open workbooks:
>
> ? ? ? ?nBooks <- xl[["Workbooks"]]$Count();
> ? ? ? ?for (i in seq_len(nBooks))
> ? ? ? ? ? ? ?
?xl[["Workbooks"]]$item(i)$Close(SaveChanges=FALSE);
>
> 2) The excel application reference does not seem to be really released
until the garbage collector runs. So this may help:
>
> ? ? ? ?xl$Quit();
> ? ? ? ?xl <- NULL;
> ? ? ? ?gc();
>
> 3) Ensure you don't have any "hanging" reference to the excel
application (sometimes from previous runs of your code that failed). If your
code is inside a function, wrap the code in point 1 and 2 on an on.exit() call
to ensure that excel is properly closed each time.
>
>
> Hope this helps.
>
> Enrique
>
> ------------------------------
>
> Date: Wed, 8 Jul 2009 16:06:57 +0300
> From: Lauri Nikkinen <lauri.nikkinen at iki.fi>
> Subject: [R] RDCOMClient: how to close Excel process?
> To: r-help at stat.math.ethz.ch
> Message-ID:
> ? ? ? ?<ba8c09910907080606n29b8a537uac5ba1788376ff42 at
mail.gmail.com>
> Content-Type: text/plain; charset=windows-1252
>
> Hi,
>
> I?m using R package RDCOMClient (http://www.omegahat.org/RDCOMClient/)
> to retrieve data from MS Excel workbook. I?m using the code below to
> count the number of sheets in the workbook and then loop the data from
> sheets in to a list.
>
> ############# R code ###################
> library(gdata)
> library(RDCOMClient)
>
> xl <- COMCreate("Excel.Application")
> sh <-
xl$Workbooks()$Open(normalizePath("sample_file.xls"))$Sheets()$Count()
>
> DF.list <- list()
> for (i in 1:sh) {
> ? DF.list[[i]] <- read.xls("sample_file.xls", sheet=i,
> stringsAsFactors = FALSE)
> ? }
> ######################################
>
> COMCreate opens Excel process and it can be seen from Windows Task
> Manager. When I try to open sample_file.xls in Excel, it just flashes
> in the screen and shuts down. When I kill (via task manager) the Excel
> process COMCreate started, sample_file.xls will open normally.
>
> The question is, how can I close the Excel process COMCreate started.
> xl$Close() doesn?t seem to work. The same problem have been presented
> in this post to R-help:
> http://tolstoy.newcastle.edu.au/R/help/06/04/25990.html
>
> -L
>
>
>
> ------------------------------
>