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?
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
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.
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
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
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.
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’)
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”
This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.