[R] Automatic datasets creation from multiple data sheets in a single excel file

Erich Neuwirth erich.neuwirth at univie.ac.at
Sat Aug 1 20:26:24 CEST 2009

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
    For Each ws In wb.Sheets
        RInterface.PutDataframe ws.Name, ws.Cells(1, 1).CurrentRegion
    Next ws
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

More information about the R-help mailing list