World: r3wp

I have to automate a process of retrieving contents of certain cells 
and comparing them to other cells and then emailing the information 
to persons found in certain cells.

Have you checked the Library? This might help:


http://www.rebol.org/cgi-bin/cgiwrap/rebol/ml-display-message.r?m=rmlLHMQ

I'll look into it.

thank.

Of course, it's no where near so difficult if you can export a CSV 
file from Excel first.

Actually, I find it not difficult at all with a tab-delimited text 
file but not sure I can get the file brought to me in that format 
and not sure if I can automate the conversion to that type.

I did an Excel automation dialect with Robert Muench a few years 
ago.

It was for Excel 9 I think.

Yeah I actually found that but it seems more tailored to creating 
excel spreadsheets wasn't it?  Maybe I misunderstood what it was.

Using the comlib allows you to access indivdual cells in an Excel 
spreadsheet

It was for programmatic control. You can read and write cells and 
ranges, etc.

Do keep in mind, recent copies of Excel "are" simply XML.

where is the comlib found at?

I just looked over comlib but looks like you gotta know comlib functions 
already and the documentation was a bit lacking for me.

I'll have to doublecheck tomorrow Reichart.

Where do I find COM commands or reference materials.  I know nothing 
about COM but want to try Anton's COMLIB stuff.

MSDN

ok. I see if I can find it there.

The main thing to remember is that the only thing you do with COM 
is tell other things to do stuff. Look to the developer's documentation 
for the things you want to do stuff.

I want to be able to read cells data from EXCEL.  Could I do that 
with COM?  I can't find a way to automate the conversion of excel 
spreadsheets to tab deliminated format so I think I gotta work directly 
with the spreadsheet data.  Which sucks.

I pitched the idea and now I gotta make it work.  LOL.

Yup, you can do that with COM. There are standalone conversion tools 
you can download that can do it too.

Yeah but I can only use REBOL - we can't introduce any software other 
than what we already have.  I see a ton of conversion mods out there 
but that wont  be allowed.

Can you use other scripting tools? Not that they would be better 
to use than REBOL in general, but COMLib may not be as good as a 
language with ActiveScripting support.

Good excuse to learn COMLib though :)

We have access to other scripting tools but not allowed on this project 
because I said I would only be using REBOL and the application (EXCEL)

I'm looking at MSDN in the COM section and browing thru - I look 
days away from learning how to do this.

Well, there you go. Look in MSDN in the Office API section.

Which version of Excel?

2003

Start here: http://msdn.microsoft.com/en-us/library/aa272254(office.11).aspx

I'll check it out.

Thanks Brian.

The VBA object model is the COM object model, just friendlier.

Ok, this is all GREEK to me at this point but checking into it.

Gotta go. Good luck!

Heh, thanks.

Brian, I got this code for VBA stuff:

Dim xlApp As Object = CreateObject("Excel.Application")
Dim xlWB As Object = xlApp.Workbooks.Open("C:\Path\Filename.xls")
xlWB.Worksheets(1).Select()

xlWB.SaveAs(Filename:="C:\Path\FileName.txt", FileFormat:=-4158, 
CreateBackup:=False)
xlWB.Close()
xlWB = Nothing
xlApp.Quit()
xlApp = Nothing

It looks like it is supposed to automate opening an Excel doc and 
then converting it to save it as a tab-deliminated file which would 
be perfect for me to then use REBOL to parse that file.  However, 
I tried saving this data in file with a .vbs extension but it gives 
me an error after the first line which says "Expected End of Statement". 
 I have no clue how to fix it as this code came off the net.

I'm sure I'm doing something wrong but not sure what that is.

Two things I've done--mention before I believe. One is an Excel control 
dialect. It doesn't let you control everything, and requires a non-REBOL 
DLL I wrote, which is free to use. The other is a send-keys dialect 
that lets you pump keystrokes to apps. You should be able to do the 
above process with that.

The above code looks like regular VB. I never did vbs, so I don't 
know detail differences. For example, you can run VBA in a host app 
(e.g. Excel) that uses it as a macro language, but maybe the command 
prompt doesn't support all the same things, like variable declarations.

It looks like it's just line-wrapping in altme that's breaking the 
lines. VB is line oriented, requiring a line continuation character 
for multiline statements.

Does the WSH give you any other info about the error?

Yeah it looks like it breaks at the 11 character on line 1.

I actually have another script (its at work) and it uses ADO.  But 
it gives me an error but does seem to out put the data.  I also think 
I have another route which is to record a macro in excel save the 
macro to another workbook and then modify the macro to launch on 
open.  I haven't figured out the code to launch a macro on open of 
the workbook but did find out how to launch a message on open.  So 
I think I'm close.

If I go the macro route then I can use the call command to call excel 
and the macro workbook which will perform actions on the other excel 
file.

kinda ugly way of doing it I suppose but gets the job done.

what about  a microsoft tool called Log Parser?  It allows command 
line access to files and can also do things like file format conversions 
and simple manipulations etc.   http://www.microsoft.com/technet/scriptcenter/tools/logparser/default.mspx