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
ADO DAO
plus
ADSI LDAP CDO
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.

Microsoft Access Database Engine 2010 Redistributable 

 Keywords:  Excel XLSX Ace 12.0 Provider ace.oledb.12.0 Win7 7 Microsoft Access Database Engine 2010 Redistributable Jet 4

Question:

I need to read an Excel xlsx file from a server without Excel installed. Can this be done with Winbatch? Just open, read the rows and close.

Answer:

Previously you could use ADO and the Microsoft Jet provider 4.0 for XLS files. However, apparently you will now need the Ace 12.0 Provider to deal with .XLSX files.

This of course assumes you have the Ace 12.0 Provider installed on the system. It looks like you can download and install the necessary Ace 12.0 Provider which is included with the Microsoft Access Database Engine 2010 Redistributable: http://www.microsoft.com/en-us/download/details.aspx?id=13255

You can then use ADO to communicate with the XLSX files. For more on ADO see: 

;***************************************************************************
;**
;**                     ADO
;**
;***************************************************************************

; Constant meanings
; adStateClosed 0 The object is closed
; adStateOpen 1 The object is open
; adStateConnecting 2 The object is connecting
; adStateExecuting 4 The object is executing a command
; adStateFetching 8 The rows of the object are being retrieved


provider = 'Provider=Microsoft.ACE.OLEDB.12.0'
sNWind = DirScript():'Northwind.accdb'


Names = `Order ID,Employee ID,Customer ID,Order Date,Shipped Date,Shipper ID,Ship Name,Ship Address,Ship City,Ship State/Province,Ship ZIP/Postal Code,Ship Country/Region,Shipping Fee,Taxes,Payment Type,Paid Date,Notes,Tax Rate,Tax Status,Status ID`
Values = `30,9,27,2006:01:15:00:00:00,2006:01:22:00:00:00,2,Karen Toh,789 27th Street,Las Vegas,NV,99999,USA,200,0,Check,2006:01:15:00:00:00,,0.0,,3`

; Create a Recordset from all the records in the Orders table
oConn = ObjectCreate('ADODB.Connection')
oRs = ObjectCreate('ADODB.Recordset')
oConstants = ObjectConstantsGet( oConn )

cmdstring = provider:';Data Source="' : sNWind :'"'
oConn.Open( cmdstring )
oConn.CursorLocation = oConstants.adUseClient

oRs = oConn.Execute("Orders", , oConstants.adCmdTable)

ret = ObjectTypeGet( oConstants )
If ret != 'DISPATCH'
     Pause('Notice','Not Dispatch Type - required by Winbatch.')
     Goto Cleanup
EndIf

; Check Connection State -
state = -1
state = oConn.state
If state != oConstants.adStateOpen
     Pause('Notice','Connection State Not Open.')
     Goto Cleanup
EndIf

; Check Recordset State - Returns a value that describes if the Recordset object is open, closed, connecting, executing or retrieving data
state = -1
state = oRs.state
If state != oConstants.adStateOpen
     Pause('Notice','Record State Not Open.')
     Goto Cleanup
EndIf
count = oRs.Fields.count
fcount = ItemCount( Names, ',' )
If count != fcount
     Pause('Notice','Expected ':fcount:' Fields.')
     Goto Cleanup
EndIf

reccount = oRs.RecordCount
Pause('Record Count',reccount)

pos = oRs.AbsolutePosition
;Pause('AbsolutePosition', pos)
;Pause('index', oRs.Index)

For x = 0 To count -1
   name =  oRs.Fields(x).name
   value =  oRs.Fields(x).value
   If name != ItemExtract( x+1, names, ',' ) Then retvalue = 0
   If value != ItemExtract( x+1, values, ',' ) Then retvalue = 0
   Pause(x,name: @LF: value)
Next

data = oRs.GetString
Pause('GetString',data)


oRs.MoveFirst
While !oRs.eof
   orderid =  oRs.Fields('Order ID').value

   ; Random sampling of data
   If orderid == 30
      If oRs.Fields('Ship Name').value != 'Karen Toh'
         Pause('Notice','"Ship Name" field doesn't match.')
         Goto Cleanup
      EndIf
   EndIf
   If orderid == 50
      If oRs.Fields('Ship Name').value != 'John Rodman'
         Pause('Notice','"Ship Name" field doesn't match.')
         Goto Cleanup
      EndIf
   EndIf
   If orderid == 78
      If oRs.Fields('Ship Name').value != 'Soo Jung Lee'
         Pause('Notice','"Ship Name" field doesn't match.')
         Goto Cleanup
      EndIf
   EndIf

   oRs.MoveNext
EndWhile

:CleanUp
//***************************************************************************
//**
//**   Close all ADO handles
//**
//***************************************************************************

; Close Recordset
oRs.Close()

; Check Recordset State - expect adStateClosed
state = oRs.state
If state != oConstants.adStateClosed
     Pause('Notice','Record State Not Closed.')
EndIf


; Close Connection
oConn.Close()

; Check Connection State - expect adStateClosed
state = oConn.state
If state != oConstants.adStateClosed
     Pause('Notice','Connection State Not Closed.')
EndIf

oConstants = 0
oRs = 0
oConn = 0

Pause('Notice', "All Done!")
Exit

Article ID:   W18041

Filename:   Microsoft Access Database Engine 2010 Redistributable .txt
File Created: 2013:01:11:10:15:42
Last Updated: 2013:01:11:10:15:42