ADO Excel read/write with open connection and excel obj?

[Jfish]

All: I am a COM newbie and don't have a detailed knowledge of ADO. However, I was recently able to create a connection to an Excel spreadsheet and perform queries. I was blown away by the power of this. Then I wanted to write records using the CopyFromRecordset method as in: $oSheet.Cells(1,1).CopyFromRecordset($objRecordSet).

The problem is that I think I have to either: (i) write the recordset to another object or (ii) close my connection before opening the Excel object that was the ADO datasource (which purges the recordset on close). I am wondering:

1.) if my understanding of this is correct, and

2.) if so, are there are any workarounds that folks use to write the data back with this method? I have tried the SQL to insert the records but I have a column that contains a string called "TRUE" and the ADO converts this to -1 for some reason with a global insert command ... but it is "TRUE" when the copyrecord set is used to write to a separate spreadsheet.

3.) If my assumptions are not correct, what am I missing?

Any help would be greatly appreciated.

[hzrqishk]

Hi.

Solved this problem?

[water]

The wiki has an ADO tutorial now which might help you.