Search code examples
excelautomationautomationanywhere

Unable to use Excel as Database in Automation Anywhere 10

I am trying to use Excel as Database in Automation Anywhere 10. Following is my code

Connect to "Provider=Microsoft.ACE.OLEDB.16.0",Data Source = C:\Users\myuser\Documents\demo.xlsx;Extended Properties="Excel 10.0 Xml;HDR=YES";"Session:"session1"
Execute SQL statement:'Select * from [Sheet1]$'

But it is showing the following error,

Provider cannot be found. It may not properly installed.

Can anyone help on this?

Solution

  • Try

    dim cn as object, cnstr as string, rs as object

set cn = createobject("adodb.connection")
cnstr = "Provider=Microsoft.ACE.OLEDB.16.0,Data Source=C:\Users\myuser\Documents\demo.xlsx;Extended Properties=""Excel 10.0 Xml;HDR=YES"";Session:""session1"""
set rs = cn.Execute("Select * from [Sheet1]$")

    some version EXCEL use Provider=Microsoft.ACE.OLEDB.12.0 instead of Provider=Microsoft.ACE.OLEDB.16.0.