Surya's Sandbox

"""
Export or Copy ADODB Recordset to Excel Spreadsheet
@Author Surya Nyayapati
@Date Jun-25-2009
"""
 
import clr
 
from System.Reflection import Assembly
from System.Reflection import Missing
 
#From C:\WINDOWS\assembly Copied the Display name for ADODB
clr.AddReferenceByName('ADODB, Version=7.0.3300.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a')
from ADODB import ConnectionClass, RecordsetClass, CursorLocationEnum
 
connString = "DSN=myDsn;Uid=myUsername;Pwd=myPassword;"
 
conn = ConnectionClass()
rs = RecordsetClass()
conn.Open(connString)
 
conn.CursorLocation = CursorLocationEnum.adUseClient
query = "SELECT * FROM user_group" # Any SELECT SQL
rs = conn.Execute(query)
"""
#To iterate over Recordset
while rs.EOF <> True:	
	print "%s - %s" %(rs[0].Item[0].Name.ToString(),rs[0].Item[0].Value.ToString())
	print "%s - %s" %(rs[0].Item[1].Name.ToString(),rs[0].Item[1].Value.ToString())	
        # ...
	rs.MoveNext()
"""	
 
#Start a New Excel Instance
VSTOpath = "C:\\Program Files\\Microsoft Visual Studio 9.0\\Visual Studio Tools for Office\\PIA\\Office11\\"
 
#Load and add reference to Excel dll