Unable to use Excel as Database in Automation Anywhere 10

Question

I am trying to use Excel as a 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 with this?

Answer 1

Hi, you might be facing this issue as some version EXCEL use Provider=Microsoft.ACE.OLEDB.12.0 instead of Provider=Microsoft.ACE.OLEDB.16.0.

so rectify this just try using the code given below to connect

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]$")