Before now i used this query to read data from large excel files “Select * from [”+ in_CompilationSheetName+“$]” and i use the following to connect to the excel using uipath data base activity “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=”+ExcelFilePath+“;Extended Properties=”“Excel 12.0 Xml;HDR=YES”“”, this works because i have Microsoft access installed in my system.
Now my issue is that i am looking for a query that could get the list of sheets in the excel.
A simple way would be to use an Excel Scope activity and point to the file you want then in the Output property you use Control+K to create a variable for your Workbook and inside the activity you can get the Sheet names by using assign activity and the function: varMyWorkbook.GetSheets().
hello thanks, but the excel is so large and the excel scope takes a lot of time . that is why I started using query to read the excel, and the query is really fast.
But now i need to get the list of sheets using the query too
I will do a little test and be right back. This how i could do, without uipath connection activity (maybe you can adjust if want to use it):
1. myConn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\UserData\Downloads\Busca_Email_20200708_2017.xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES';")
2. Invoke Method
Target: myConn
Method: Open
3. myDataTable = myConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, Nothing)
4. For Each Data Row
4.1 varSheetName = row("TABLE_NAME").ToString