Jump to content

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


Recommended Posts

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.

Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

Link to post
Share on other sites
  • 2 years later...

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (NEW 2021-06-14 - Version 1.6.5.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (NEW 2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...