Clark,

I agree with Vitor that working in R might be easier, but it seems that you
are working in the excel VBA environment and there may be good reason why
you want to do so that we don't know about.  If so, why use Rexcel function
to read the file into excel when you can use VBA code to open the file in
excel and then you can send the data you need to analyse to R using Rexcel?

And of course the great thing about VBA is that if you don't know how to
code what you want to do, you can always record it as a macro and then view
the code (a neat feature that S-PLUS has too).
Good luck and please follow up with more questions if our suggestions are of
no help to you.

Thanks,

Roger



On 12/31/05, Vitor Chagas <vitorchagas@yahoo.com> wrote:
>
> Hello Allan,
>
> You can work in two different ways, from Excel using
> RExcel or from R with RODBC. Personally i prefer
> working from R.
>
> You can start by giving names to the excel ranges
> (remember to put the var names in the 1st line), then
> run the following code
> to select the excel spreadsheet
>
> library(RODBC)
> # Select XLS File
> xls.file = choose.files(filters = "*.xls")
> workdir = unlist(strsplit(xls.file, "\\\\"))
> workdir = paste(workdir[-length(workdir)],
> collapse="/")
> setwd(workdir)
> # Connect to XLS Data
> channel <- odbcConnectExcel(xls.file)
>
> after this u can check what tables (ranges) are
> available to work with
>
> sqlTables(channel)
>
> if you have a range named ŤClaimsť, use something like
> this to load it in to R
>
> xlClaims = sqlQuery(channel, paste("SELECT * FROM
> Claims"))
>
> and close the connection with
>
> close(channel)
>
>
> Hope it helps, and sorry for my poor english. Best
> regards,
>
> Vitor
>
>
> --- Clark Allan <Allan@STATS.uct.ac.za> wrote:
>
> > hello all
> >
> >
> > i have a quick question. i have been using the RODBC
> > library (trying to
> > read Excel data
> >  into R but i am doing this by using Rexcel. this is
> > probably not the
> > correct forum -
> > sorry for this).
> >
> > my code is shown below:
> >
> > Sub A()
> >
> >     'start the connection to R
> >     Call RInterface.StartRServer
> >
> >     RInterface.RRun "library(RODBC)"
> >     RInterface.RRun "A =
> > odbcConnectExcel('c:/TRY.xls')"
> >
> >     RInterface.RRun "q1 = sqlFetch(A, 'Sheet1')"
> >
> >     RInterface.RRun "odbcClose (A)"
> >
> >     Worksheets("out").Activate
> >
> >     Call RInterface.GetArray("q1", Range("A1"))
> >
> >     Call RInterface.StopRServer
> >
> > End Sub
> >
> >
> > i have included four examples below. on the left
> > hand side we have the
> > data as it appears
> >  in Excel and on the right hand side we have the
> > output from the code
> > (outputted to the
> >  'out' sheet in excel). in the first example the
> > code works while in the
> > other three
> > exampl0es it does not. ('a' is some character) when
> > i use the commands
> > in r directly everything works correctly (ie missing
> > values are treated
> > as NA - <characters is treated similarly>)
> >
> > can anyone show me how to solve this!
> >
> > ANOTHER QUESTION: am i allowed to have numeric and
> > character values in
> > the same column when importing from Excel to R?
> > (seems like i cant)
> >
> > thanking you in advance!
> >
> > wishing you all a happy new year (in advance)
> > /
> > allan
> >
> >
> > Y     X1      X2              1       6       3
> > 1     6       3               2       6       2
> > 2     6       2               3       5       2
> > 3     5       2
> >
> >
> > Y     X1      X2              0
> > 1     6       3
> > 2     6       2
> > 3     a       2
> >
> >
> > Y     X1      X2              0
> > 1     6       3
> > 2     6       2
> > 3     a       2
> >
> >
> > Y     X1      X2              0
> > 1             3
> > 2     6       2
> > 3     5       2>
> ______________________________________________
> > 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
>
> ______________________________________________
> 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
>

	[[alternative HTML version deleted]]


