Hello Charilaos-
I admit that using rcom commands to create other objects are rather
confusing. My general premise is this: comGetProperty and
comSetProperty, are used for property events. However, comInvoke is
used for methods and objects. In theory, it's pretty simple, but how
does one tell the difference between a property versus a method using:
comGetObjectInfo...well one cannot.
So, to get around this issue I use the Help menu item from the VBA
editor window: Help >> Microsoft Visual Basic Help >> Microsoft Excel
Visual Basic References. Here you will find each parameter that
can be
manipulate via rcom; it clearly indicates which are properties,
methods,
objects and event etc. This has proven most helpful to me as I use
rcom
to dump my subsequent results into Excel, PowerPoint, Word, or
Outlook.
Also, I use the following code to better view the parameters that
comGetObjectInfo() returns:
This allows you to view parameters in a more efficient manner
jnk1<-comGetObjectInfo(wb)
info.jnk<-matrix(jnk1,nrow=length(jnk1),ncol=4,byrow=T)
de(info.jnk)
Also, here is some code written explicitly for Excel, I hope this
helps!
#Create Excel Object
excel<-comCreateObject("Excel.Application")
comSetProperty(excel,"Visible",T)
comSetProperty(excel, "SheetsInNewWorkbook", 1)
wb <- comGetProperty(excel, "Workbooks")
wb <- comInvoke(wb, "Add")
#Change the name of a specific worksheet
comSetProperty(
comGetProperty(wb,"Worksheets",1),
"Name","Requested Asset Information")
#Select a specfic sheet
sheet<-comGetProperty(
comGetProperty(wb,"Sheets"),
"Item",1)
comInvoke(sheet,"Activate")
#Determine last column of header rows for FONT manipulation
last.excel.col<-paste(c(LETTERS)[dim(asset.tmp)[2]],'1',sep='')
r2<-comGetProperty(sheet,"Range","A1",last.excel.col)
comSetProperty(r2,"Value",names(dcc.asset.list))
#Now, Bold the Text.
comSetProperty(
comGetProperty(
comGetProperty(r2,"CurrentRegion"),
"Font"),
"Bold",T)
#Then, increase the font to: 14
comSetProperty(
comGetProperty(
comGetProperty(r2,"CurrentRegion"),
"Font"),
"Size",14)
#Now, Autofilter the column headings.
comSetProperty(r2,"Autofilter",T)
#Determine last row for FONT manipulation
last.excel.row2<-paste(c(LETTERS)[dim(asset.tmp)[2]],
dim(asset.tmp)[1]+1,
sep='')
r3<-comGetProperty(sheet,"Range","A2",last.excel.row2)
projects<-as.matrix(asset.tmp)
comSetProperty(r3,"Value",projects[1:nrow(asset.tmp),])
#Now, Autofit columns
excel.col1<-paste('A:',c(LETTERS)[dim(asset.tmp)[2]],sep='')
r4<-comSetProperty(
comGetProperty(sheet,"Columns",excel.col1),
"Autofit",T)
#Now, Center align the Text.
comSetProperty(
comGetProperty(r2,"CurrentRegion"),
"HorizontalAlignment",3)
#Finally, save workbook to appropriate location
comInvoke(
wb,
"SaveAs",
paste('c:/temp/Assets/',my.client,'/',my.dow.code,'/',
'Asset Location(s) ',c.month,
'-',c.year2,'.xls',sep='')
)
comInvoke(wb,"Close")
comInvoke(excel,"Close")
Thanks, and good luck!
D-
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
Charilaos
Skiadas
Sent: Wednesday, November 14, 2007 6:43 AM
To: R ((D))COM and RExcel server related issues
Subject: [Rcom-l] Resources for calling Excel from R
Let me try this again:
Hello all,
I'm new to rcom, and my focus is to access Excel from R. For
instance, I would like to attempt to read the current spreadsheet.
The problem I am running into is how to find out the proper calls. I
started with looking at the examples in comGetProperty, and in
particular managed to get the following call working:
r <- comGetProperty(ws,"Range","A1","B4")
My question really is: How could I have figured out the syntax if
noone told me about it?
What I tried is looking at:
comGetObjectInfo(ws)
The output from that is pretty chaotic, so then I tried:
sort(names(comGetObjectInfo(ws)))
This would show me that one of my options there is "Range", but how
can I find out the proper incantation?
For instance, I am after getting all the data from the spreadsheet,
without having to specify the range. I thought that "ShowAllData"
would do it, but it didn't work. I tried the following:
comGetProperty(ws,"ShowAllData")
But that didn't seem to work.
My main problem is that I feel I am blindly trying things out in the
hope one of them would work I find the output of comGetObjectInfo not
very helpful, but perhaps I am misusing it. Is there any way to get
more details on these functions/properties? What is each supposed to
return, and what do we need to feed into it? I suppose this has to do
with the COM interface? Is this stuff available somewhere, or is it
really guessing? I'm hoping for the former.
Thanks for your time,
Haris Skiadas
Department of Mathematics and Computer Science
Hanover College
PS: I tried to find answers in the archives, but I got the impression
that most people use rcom the other way around. Just curious why that
is the case.
