GetSheets with code activity and vb.net

Help
1

Hi,

Everybody knows that if you want to obtain the names of excel sheets you have to use Excel application scope, get the wb as variable and then wb.GetSheets.

BUT!

If you don’t have excel installed this will cause an exception.

SO…

How can we read the excel sheets with Invoke code activity and vb.net without office installed?
Somebody has an example?

2

These may help you get started. The first one shows how create and use an Excel workbook object, and the second shows how to read the sheets.

1 Like
3

@KevinDS,

Try this code,

Dim xlApp As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application()
    Dim excelBook As Microsoft.Office.Interop.Excel.Workbook = xlApp.Workbooks.Open("D:\Book1.xlsx")
    Dim excelSheets As String() = New String(excelBook.Worksheets.Count - 1) {}
    Dim i As Integer = 0

    For Each wSheet As Microsoft.Office.Interop.Excel.Worksheet In excelBook.Worksheets
        excelSheets(i) = wSheet.Name
        i += 1
    Next
1 Like
4

thank you @sarathi125 it works!

There was some problem with de invoke code with an error BC31539 but I’ve found the solution right here:

5

@sarathi125 one question… how can I close the workbook in this code?

6

@KevinDS,

Use these lines.

excelBook.Close(0)
xlApp.Quit()
1 Like
7

@sarathi125 I got some questions. It looks like this only work if you have office installed. right?

it’s giving me error in machines without office.

8

@KevinDS,

Try with this code,

Dim fileName = "C:\ExcelFile.xlsx"
    Dim connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & fileName & ";Extended Properties=""Excel 12.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text"""

    Using conn = New OleDbConnection(connectionString)
        conn.Open()
        Dim sheets = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
1 Like
9

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.