WinBatch Tech Support Home

Database Search

If you can't find the information using the categories below, post a question over in our WinBatch Tech Support Forum.

TechHome

WinBatch
OLE COM ADO CDO ADSI LDAP
OLE with Excel
plus
Samples from Users

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

Playing Around with ADO and Updating Excel Columns 

Keywords:   Playing Around with ADO and Updating Excel Columns 

; ///////////////////////////////////////////////////////////
; Playing Around with ADO and Updating Excel Columns       //
;                                                          //
; This assumes you have Tab(s) on an Excel Workbook that   //
; can be considered databases                              //
;                                                          //
; You will seee a lot of stuff commented out, but it       //
; appears  that the Jet 4.0 Provider will update columns   //
; whereas the Excel Driver itself will give an error       //
; Just point the cConn = line to reflect where you placed  //
; the testvs.xls file.                                     //
;                                                          //
; Stan Littlefield 10/21/2001                              //
; ///////////////////////////////////////////////////////////

#DefineFunction isMDAC()
Return( RegExistKey(@RegClasses,"ADODB.Connection") )
#EndFunction

#DefineFunction HasTables(cConn)
CAT                  = ObjectOpen("ADOX.Catalog")
CAT.ActiveConnection = cConn
tbls                 = CAT.Tables
n                    = tbls.Count
lRet                 = (n>0)

If lRet
   For i = 0 to n-1
      t=CAT.Tables(i)
      j= i+1
      message("Table %j%",t.Name)
   Next
Endif

ObjectClose(tbls)
ObjectClose(CAT)
Return( lRet )
#EndFunction

;====== start of script =====

If ! isMDAC()
   message("Error:Cannot Continue","MDAC Must Be Installed. Contact IS")
   exit
Endif


adOpenDynamic    = 2
adLockOptimistic = 3
adCmdTable       = 2
adCmdTableDirect = 512
adCmdText        = 1
cXLS             = "E:\WBDEMO\DATA\TESTVS.XLS"

;uncomment this connection and comment out the Jet Provider
;the script should fail when trying to update
;probably should comment out Hastables() call also
;cConn = "Provider=MSDASQL.1;Driver={Microsoft Excel Driver (*.xls)}; DBQ=%cXLS%;ConnectionTimeout=30"



cConn = "Provider=MicroSoft.Jet.OLEDB.4.0; Data Source=%cXLS%; Extended Properties=Excel 8.0;"


If ! HasTables( cConn )
   Message("Unable To Continue","WorkBook %cXLS% Has No Defined Data Tables")
   Exit
Endif


DB               = ObjectOpen("ADODB.Connection")
RS               = ObjectOpen("ADODB.Recordset")
DB.Open(cConn)

;If using the Excel Driver syntax requires [ ] delimiters
;RS.Open("[Tables$]",DB,adOpenDynamic,adLockOptimistic,adCmdTableDirect)

;If Using OLEDB Driver
RS.Open("Tables$",DB,adOpenDynamic,adLockOptimistic,adCmdTableDirect)


; routine to update 'fields' with ADO
; treats the worksheet like a normal recordset
RS.MoveFirst()

While ! RS.eof()
   fld           = RS.Fields("TABLE TYPE")
   fld1          = RS.Fields("TABLE NAME")
   v             = fld.Value
   If v=="SYSTEM TABLE"
     fld.Value  = "ACCESS TABLE"
      RS.Update()
      message("Changing Back To SYSTEM TABLE",fld1.Value)
   Endif
   If v=="ACCESS TABLE"
     fld.Value  = "SYSTEM TABLE"
      RS.Update()
      message("Changing Excel Column To ACCESS TABLE",fld1.Value)
   Endif

   RS.MoveNext()
Endwhile

RS.Close()
ObjectClose(RS)
DB.Close()
ObjectClose(DB)
Message("Certain Column Values in the Tables Worksheet Updated","Open testvs.xls and check if Changes Took")
Exit

Article ID:   W15639

File Created: 2003:05:13:11:29:20
Last Updated: 2003:05:13:11:29:20