Access Excel

First off, I’m relatively new to Ruby. What I’m trying to do is access a
specific Excel Workbook that is alreay open. I already know the
following:

  • access running Excel - WIN32OLE.connect(“Excel.Application”)
  • access unsaved Excel Workbook - WIN32OLE.connect(“Book1”)

What I’m having difficuly with is connecting to a specific Excel object
(ie - if you open Excel via the Start menu, it creates a new Excel
object). One way I’ve been able to do this in another language would be
to use the full workbook name for the moniker, rather than “Book1”. This
doesn’t seem to work with WIN32OLE.connect(moniker), however, that
wouldn’t be my preferred method. (if someone could let me know wha the
moniker would be to connect to a running workbook that has already been
saved, I would appreciate it)

The method I use in another language is calling
“AccessibleOjbectFromWindow” for the “EXCEL7” ChildWindow. However, I
can’t seem to get this to work, and I haven’t been able to locate any
Ruby examples for this dll call. Could someone provide an example of
calling “AccessibleOjbectFromWindow” for an Excel “EXCEL7” ChildWindow?

I am just learning Ruby… I found this:

If a blog:
http://rubyonwindows.blogspot.com/2008/01/rubygarden-archives-scripting-excel.html

Opening spreadsheets, accessing workbooks and worksheets

excel = WIN32OLE::new(‘excel.Application’)
workbook = excel.Workbooks.Open(‘c:\examples\spreadsheet.xls’)

Hope it helps.
Jody

Opening spreadsheets, accessing workbooks and worksheets

excel = WIN32OLE::new(‘excel.Application’)workbook =
excel.Workbooks.Open(‘c:\examples\spreadsheet.xls’)worksheet =
workbook.Worksheets(1) #get hold of the first worksheetworksheet.Select
#bring it to the front -need sometimes to run macros, # not for
working with a worksheet from rubyexcel[‘Visible’] = true #make visible,
set to false to make invisible # again. Don’t need it to be visible
for script to work

Jody L. Meyer wrote:

Opening spreadsheets, accessing workbooks and worksheets

excel = WIN32OLE::new(‘excel.Application’)
workbook = excel.Workbooks.Open(‘c:\examples\spreadsheet.xls’)

Thank you for your response. I prolly should have phrased my question
better. I know how to access the workbook if I already have access to
the correct Excel object. My question is how do I access an already
running Excel object (or process) if there is more than 1 running
(access a specific one). Again, the best way I know how is by calling
“AccessibleOjbectFromWindow”, and accessing the workbook object from the
“EXCEL7” ChildWindow. If someone knows a better way, I would appreciate
that knowledge. Otherwise, if someone could provide a simple example of
this dll call, I would appreciate that as well.

I am just learning Ruby… I found this:

If a blog:
http://rubyonwindows.blogspot.com/2008/01/rubygarden-archives-scripting-excel.html

Opening spreadsheets, accessing workbooks and worksheets

excel = WIN32OLE::new(‘excel.Application’)
workbook = excel.Workbooks.Open(‘c:\examples\spreadsheet.xls’)

Hope it helps.
Jody

Opening spreadsheets, accessing workbooks and worksheets

excel = WIN32OLE::new(‘excel.Application’)workbook =
excel.Workbooks.Open(‘c:\examples\spreadsheet.xls’)worksheet =
workbook.Worksheets(1) #get hold of the first worksheetworksheet.Select
#bring it to the front -need sometimes to run macros, # not for
working with a worksheet from rubyexcel[‘Visible’] = true #make visible,
set to false to make invisible # again. Don’t need it to be visible
for script to work

jeth row wrote:

Jody L. Meyer wrote:

Opening spreadsheets, accessing workbooks and worksheets

excel = WIN32OLE::new(‘excel.Application’)
workbook = excel.Workbooks.Open(‘c:\examples\spreadsheet.xls’)

Thank you for your response. I prolly should have phrased my question
better. I know how to access the workbook if I already have access to
the correct Excel object. My question is how do I access an already
running Excel object (or process) if there is more than 1 running
(access a specific one). Again, the best way I know how is by calling
“AccessibleOjbectFromWindow”, and accessing the workbook object from the
“EXCEL7” ChildWindow. If someone knows a better way, I would appreciate
that knowledge. Otherwise, if someone could provide a simple example of
this dll call, I would appreciate that as well.

If the workbook has already been saved and you know the path:

wb = WIN32OLE.connect(‘C:\Path To File\MyWorkbook.xls’)

This returns an instance of the Excel.Workbook object. You can then grab
an instance of that Workbook’s Application object:

xl = wb.Application

Or, of course, shorten it to a singe line:

xl = WIN32OLE.connect(‘C:\Path To File\MyWorkbook.xls’).Application

Hope that helps.

David
http://rubyonwindows.blogspot.com/

David M. wrote:

xl = WIN32OLE.connect(‘C:\Path To File\MyWorkbook.xls’).Application

Thank you David. I had been trying this, but using:

xl = WIN32OLE.connect(“C:\Path To File\MyWorkbook.xls”).Application

  • which doesn’t seem to work.

This brings up the question, how could a Ruby script find the full path
of a running Excel workbook? (though I would appreciate an answer here,
this question isn’t Ruby-specific, and I can find the answer elsewhere)

Still, I would appreciate an example of calling
“AccessibleOjbectFromWindow”, which is in the Oleacc.dll library. (an
example or link to an example would be great) I already know how to get
the hwnd of the “EXCEL7” ChildWindow.

W. James wrote:

This is a good place to talk about slashes in paths under windoze.

Good point.

If you think you have to use back-slashes, they must be escaped within
double quotes:

“C:\Path To File\MyWorkbook.xls”

…or enclosed within single (not double) quotes:

xl = WIN32OLE.connect(‘C:\Path To File\MyWorkbook.xls’).Application

Sorry, I should have drawn special attention to that in my original
reply.

Windoze itself recognizes forward slashes.

Be careful, though, as Excel does not recognize forward slashes in the
path. So, for example, this SaveAs() method (where wb is your Workbook
object) would fail…

wb.SaveAs(“C:/Path To File/MyWorkbook.xls”)

…but these versions would work:

wb.SaveAs(“C:\Path To File\MyWorkbook.xls”)
wb.SaveAs(‘C:\Path To File\MyWorkbook.xls’)

David
http://rubyonwindows.blogspot.com/

jeth row wrote:

First off, I’m relatively new to Ruby. What I’m trying to do is access a
specific Excel Workbook that is alreay open.

Also you can try roo gem (http://roo.rubyforge.org/)

jeth row wrote:

Thank you David. I had been trying this, but using:

xl = WIN32OLE.connect(“C:\Path To
File\MyWorkbook.xls”).Application

  • which doesn’t seem to work.

This is a good place to talk about slashes in paths under windoze.
Every program I have ever used that exists in a Linux/Unix version
and in a windoze version understands forward slashes in paths, even
when used under windoze. So you can and should say

“C:/Path To File/MyWorkbook.xls”

Windoze itself recognizes forward slashes. In the shell (DOS prompt)
I just did

cd vid/pics

giving me the prompt

C:\vid\pics>

However, this doesn’t work:

cd /vid/pics

If you think you have to use back-slashes, they must be escaped within
double quotes:

“C:\Path To File\MyWorkbook.xls”