cannot format excel cells..ADO 1

[durda]

Ive searched hi & lo before posting.
Im connecting to a spreadsheet using ADO
dim CONN As ADODB.Connection, RS As ADODB.Recordset
I cannot use Microsoft Excel 9.0 Object Library because all PC's using this program will not have MSOffice, plus I dont want to worry about licensing issues..

I can read/write to the spreadsheet without any problem. The problem is I cannot format the cells. Everytime I write to the cell I overwrite the cells format with excel's default format. Does anyone know how to format a cell with the type of connection i'm using.

FYI:
RS.Open "SELECT * FROM [sheet1$a1:S118]", CONN, adOpenDynamic, adLockOptimistic

and

CONN.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & StrPath & "Escalation.xls;" & _
"Extended Properties=""Excel 8.0;HDR=Yes"""

Thanks in advance

 

[dilettante]

Sorry.

SAMPLE: ExcelADO Demonstrates How to Use ADO to Read and Write Data in Excel Workbooks

ADO can be used strictly to transfer raw data to a workbook. You cannot use ADO to apply formats or formulas to cells. However, you can transfer data to a workbook that is pre-formatted and the format is maintained. If you require "conditional" formatting after the data is inserted, you can accomplish this formatting with Automation or with a macro in the workbook.

Of course if they don't have Office/Excel, how will they ever view the thing? Excel Viewer?

 

[durda]

thanks dilettante for direction!!!

Great reference material. One thing... the MS article stated that if the worksheet is pre-formatted it will maintain the format, but what ive noticed is that the cells im writing to are grouped cells and they become ungrouped when VB writes to the sheet.

as far as them viewing the spreadsheet they will not have to, its a background operation. The spreadsheet is filled and emailed automatically. Its grabbing info from a DB when a certain action is acomplished and sent to higher-ups.

 

[dilettante]

I understand now.

Yes, using ADO against Excel workbooks has advantages but comes with many limitations. Interestingly I have even found a free/cheap (depending on your situation) Excel alternative that creates workbooks compatible enough that ADO/Jet will operate against them! It's called 602Tab and is part of the 602Pro Suite from Software602. It doesn't (at least the older "2001" version I have) expose an automation interface so ADO/Jet's the only game in town there.

I have some similar situations coded as VBScripts instead. Since they only run on one machine that has Excel on it I used Excel Automation from the scripts. This lets me harvest data from a Jet DB and create Excel reports which are then emailed to deeper thinkers. ;-)

Since they were having trouble interpreting tables of figures I autogenerate Chart sheets too. Now if I could just create a sheet for them with a big button they could press to make the decisions for them.

Nothing like an IT organization run by non-IT people, hmm? And I thought a degree in business would have lent some comfort with numbers. Another myth busted.

 

[durda]

dilettante,
602 is great, ive been using it for almost 2 years now. Anyone reading this that wants a free/cheap alternative to MSOffice.. this is definetely for them. I think it blows Star out of the water but thats my opinion.

thanks again dilettante