World: r3wp

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

It's a command-line tool, so could easily call it from Rebol.

Does VBA under WSH not support typed declarations or inline assignment 
against them?

Not sure what that means Gregg.

Brock, I gotta use the resources on the machine with the only other 
introduction to be REBOL.

There are major rules against outside software.

Paul, okay, I recall you saying that.  But the fact that the small 
command-line tool is Microsoft, they may look the other way.  Besides, 
I don't know for sure if it would work for you, but may be worth 
taking a look at.

I don't think it can read native XLS can it?  Doesn't the XLS file 
still need to be converted first for logparser?

A closer look and no it does not.  I got some of the input and output 
formats confussed.  I thought it could read from a SQL/ODBC data 
source but appears that is only an output format.  There is a COM 
api, but that might be more complex than is worth.

After looking at it, I believe someone could easily build a more 
powerful utility using REBOL than what logparser is.

What I mean is, does it support

Dim xlApp
Dim xlApp As Object
Dim xlApp As Object = CreateObject("Excel.Application")


The first is just a declaration, the second adds a type to it (rather 
than making everything a Variant), and the third adds inline assignment 
to the declared variable.

I don't really know much about VBA.

This is a msft example of calling Excel from VBScript - http://support.microsoft.com/kb/198703/

This one is mainly JScript but shows more examples of controlling 
Excel - http://support.microsoft.com/kb/234774

Both are shown being called from IE but they should also work under 
WSH.

and to answer Gregg's question, it appears to support

	dim app
	set app = createobject("Excel.Application")

Thanks Peterwood.  Those seems helpful if I need to just call and 
interact with Excel but I need to actually retrieve rows in an automated 
fashion and be able to parse them in a tab-delimited manner.