scripting EXCEL

[FengShui1998]

Hello,

Does any body know how to "create" an Excel file using the
"Provider=Microsoft.Jet.OLEDB.4.0;" using VBscript? I can see the Excel file created but I'm looking for examples on how to add, format columns, cells and save the workbook.

Thanks for your help.

fengshui_1998

 

[broadthought]

This is not the usual way of handling Excel (or any application) from script.

The more usual way is to use CreateObject to create an instance of the object (in this case an Excel workbook) and manipulate its properties and methods.

eg-:

SET objXLwb = CreateObject("excel.workbook&quot
objXLwb.visible = true

You can then use the methods and properties of the Workbook object to do the required manipulations.(These are the same as if you were writing macros in VBA)

 

[FengShui1998]

broadthought,

I have that info already and can do it, but I would like to know how to write dirctly to the provider w/out having the application come up. This is possible but not very well documented.

Still searching....
fengshui_1998

 

[dilettante]

MS published a book called something like "Jet Database Engine Programming" I haven't seen yet. There might also be info in Office Developer Edition, but I haven't seen much other meat on this anyplace.

If you have the time to experiment, you could try creating a Jet database with ADO sorta pretending you're making an Access-type database. Be sure to set the "Jet OLEDB:Engine Type" to Excel (can't recall the right value).

Oh!

Global Const JET_ENGINETYPE_EXCEL30 = 20
Global Const JET_ENGINETYPE_EXCEL40 = 21
Global Const JET_ENGINETYPE_EXCEL50 = 22
Global Const JET_ENGINETYPE_EXCEL80 = 23
Global Const JET_ENGINETYPE_EXCEL90 = 24

Hmm... and set the "Data Source" to a .xls file I suppose.

Then create "tables" (worksheets?) and load the data into them with recordsets to make rows, etc.

I'd spit a few rows into a table, then open the thing up in Excel and see what I got.


Sorry I'm not too much help.