Microsoft Excel - How can we have GetSchema only return Sheetnames and not Filter names?

Asked By Burak Gunay on 16-Aug-10 04:26 PM

Hello,

I have an Excel file and I am getting the list of sheetnames in the Excel file using this code

Dim dt As DataTable

db.ConnectionSettings.DataSource = String.Concat("=", filePath)

Using con As DbConnection = db.CreateConnection

con.Open()

dt = con.GetSchema("Tables")

End Using

Instead of just getting Sheet1$, Sheet2$ for sheet names , I am getting back

Sheet1$, Sheet1$Database, Sheet1$_ , Sheet2$ , Sheet2$Database, Sheet2$_

I noticed when you have filters in an Excel file, they are getting returned as sheetnames.

(Database is a user defined filter name)

How can I just get back the actual sheetnames of an Excel file? I can't just look for underscores, since filter names can be set to anything by the user. ( ex: Database)

Thank you,

Burak

wally eye replied to Burak Gunay on 16-Aug-10 05:42 PM

Public Sub test()

Dim wkCurr As Worksheet

For Each wkCurr In ActiveWorkbook.Sheets

Debug.Print wkCurr.Name

Next wkCurr

End Sub

will get you there, replacing activeworkbook with the appropriate name (i.e. Workbooks("This file.xls")

Burak Gunay replied to wally eye on 16-Aug-10 05:53 PM

I want to do this by just using basic database connection functionality.

Is this possible?

Thanks.

wally eye replied to Burak Gunay on 17-Aug-10 10:15 AM

I guess I'm not familiar with that within Excel. I am curious though. Can you tell me the references you are using and how you are declaring your variables?

Burak Gunay replied to wally eye on 17-Aug-10 10:21 AM

Hi,

I basically got all the tablenames using GetSchema and then used linq to select those that end with a $ sign, since it seems the genuine sheetnames end with a $ sign, and the ranges, etc.. on a sheet do not.

I don't now if this is the ideal solution, but I can't use interop code.

Thanks.