How to read excel sheet data using VB.net or VBA or powershell

[sandeepvemula]

How to read excel sheet data using VB.net or VBA or powershell
i want to export excel sheet data as Datatable.
If i use read range activity then i am getting read range error. so i want read excel sheet through VB.net or VBA or powershell and i want to return those data as data table.
am getting below error message.

could not read range and i tried read range workbook activity also. it is also not working.

[Achal_Sharma]

Hey @sandeepvemula

Try this code.

Dim conStr As String = ""

conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"
conStr = String.Format(conStr, Path)
Dim connExcel As New OleDbConnection(conStr)
Dim cmdExcel As New OleDbCommand()
Dim oda As New OleDbDataAdapter()
cmdExcel.Connection = connExcel
'Get the name of First Sheet
connExcel.Open()
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
Dim SheetName As String = ""
If dtExcelSchema.Rows.Count > 0 Then
    SheetName = dtExcelSchema.Rows(dtExcelSchema.Rows.Count - 1)("TABLE_NAME").ToString()
End If
connExcel.Close()
'Read Data from First Sheet
connExcel.Open()
cmdExcel.CommandText = "SELECT * From [" & SheetName & "]"
oda.SelectCommand = cmdExcel
oda.Fill(dt)
dt.TableName = SheetName.ToString().Replace("$", "")
connExcel.Close()
Return dt

Happy Learning
Achal Sharma

[supermanPunch (Arpan)]

Hi @sandeepvemula ,

Have you tried using Workbook Read Range Activity as well ? If this doesn’t work check with the below Component :

Read Extra Large SpreadSheets - RPA Component | UiPath Marketplace