R help - Jul 2009 - Automatic datasets creation from multiple data sheets in a single excel file

Please let us know how to create automatic datasets from multiple data sheets
in a single excel file...

For example if there are 10 sheets in a single excel file, automatically 10
datasets need to be created at a time when i read an excel file as a whole
at once.

Please attach the necessary code if any...


-- 
View this message in context:
http://www.nabble.com/Automatic-datasets-creation-from-multiple-data-sheets-in-a-single-excel-file-tp24754120p24754120.html
Sent from the R help mailing list archive at Nabble.com.

rajclinasia wrote:
> 
> Please let us know how to create automatic datasets from multiple data
> sheets in a single excel file...
> 
> For example if there are 10 sheets in a single excel file, automatically
> 10 datasets need to be created at a time when i read an excel file as a
> whole at once.
> 
> 
The critical part is getting the names of the worksheets. 

http://tolstoy.newcastle.edu.au/R/e6/help/09/03/7736.html

For reading individual worksheets, there is lots of code around.

And a site search for "read excel worksheet" returns quite a few
references.

Dieter




-- 
View this message in context:
http://www.nabble.com/Automatic-datasets-creation-from-multiple-data-sheets-in-a-single-excel-file-tp24754120p24756406.html
Sent from the R help mailing list archive at Nabble.com.

If you have RExcel (and the necessary infrastructure, i.e.
statconnDCOM and possibly rcom) installed, the following VBA
macro will do the trick.

-=-=-=-=-Option Explicit

Sub TransferAllSheetsAsDataframes(wb As Workbook)
    Dim ws As Worksheet
    RInterface.StartRServer
    For Each ws In wb.Sheets
        RInterface.PutDataframe ws.Name, ws.Cells(1, 1).CurrentRegion
    Next ws
    RInterface.StopRServer
End Sub

Sub TransferSheetsInThisWorkbook()
    TransferAllSheetsAsDataframes ThisWorkbook
End Sub


-=-=-=-=-

You have to establish a reference to RExcelVBALib in your workbook.

The names of the sheets will be used as the names of the dataframes.



Dieter Menne wrote:
> 
> 
> rajclinasia wrote:
>> Please let us know how to create automatic datasets from multiple data
>> sheets in a single excel file...
>>
>> For example if there are 10 sheets in a single excel file,
automatically
>> 10 datasets need to be created at a time when i read an excel file as a
>> whole at once.
>>
>>
> 
> The critical part is getting the names of the worksheets. 
> 
> http://tolstoy.newcastle.edu.au/R/e6/help/09/03/7736.html
> 
> For reading individual worksheets, there is lots of code around.
> 
> And a site search for "read excel worksheet" returns quite a few
references.
> 
> Dieter
> 
> 
> 
> 
-- 
Erich Neuwirth, University of Vienna
Faculty of Computer Science
Computer Supported Didactics Working Group
Visit our SunSITE at http://sunsite.univie.ac.at
Phone: +43-1-4277-39464 Fax: +43-1-4277-39459