How to Select From Unknown Excel SheetName

Question

0.00/5 (No votes)

See more:

I am developing ETL Tool to import data from excel template and transform it before loading to sql database.

All table names ( in the excel sheets) are to be read dynamically. How do i read unknown table/Sheet name from the excel template, i have upto 20 Sheets? I have tried, [ " & SheetName & "$] or [Table_Name] ||[TableName] is not valid.

Any idea(s) on the VB.Net Codes (Framework 4) will be very much appreciated.Thanx guys

Posted 5-Jul-11 23:38pm

Mcmish

Updated 17-Aug-11 19:19pm

v3

Add a Solution

Comments

Mcmish 7-Jul-11 12:44pm

Hi Dave, any solution for me as am in dire need of solution to the seeming puzzle. Thanx

2 solutions

Solution 1

This is an example code block but you must use this reference

Imports Excel = Microsoft.Office.Interop.Excel

Code Block :

VB

Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim range As Excel.Range
        Dim rCnt As Integer
        Dim cCnt As Integer
        Dim Obj As Object

        xlApp = New Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Open("c:\vbexcel.xlsx")
        xlWorkSheet = xlWorkBook.Worksheets(1)

        range = xlWorkSheet.UsedRange

        For rCnt = 1 To range.Rows.Count
            For cCnt = 1 To range.Columns.Count
                Obj = CType(range.Cells(rCnt, cCnt), Excel.Range)
                MsgBox(Obj.value)
            Next
        Next

        xlWorkBook.Close()
        xlApp.Quit()

Posted 6-Jul-11 4:25am

Emrah Taylan

Updated 6-Jul-11 10:40am

v2

Comments

Maciej Los 6-Jul-11 16:25pm

In this line: <pre lang="vb">xlWorkSheet = xlWorkBook.Worksheets(0)</pre> probably is error. Count of worksheets start from 1 to n.

Emrah Taylan 6-Jul-11 16:40pm

Yes :) I forgat when I send answer, I am using c# :)

Mcmish 6-Jul-11 23:26pm

Emrah, I quite appreciate, thanx alot but i must clarify a point that the Excel template is define differently by names WebTMA upto 4 different templates, so i dynamically chose the particular template and am able to display all the sheets in a combobox but i must specify one of the sheets's name before it works, so am required not to specify any specific sheet name so that any1 i chose from d combobox will be use. see my code below:

Private Sub Button2_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
OpenFileDialog1.ShowDialog()
XlsPath.Text = OpenFileDialog1.FileName

Dim excelConnectionString As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & OpenFileDialog1.FileName & ";" + " Extended Properties=Excel 8.0")

'Connection to Excel Datasheet
excelConnectionString.Open()

'Fetching all sheets from the Excel Template (Function Call)
ComboBox1.DataSource = GetExcelSheetNames(SVTest:="")
'ComboBox1.DataSource = GetDataFromExcel(SVTest:="")

'OleDbCommand to Fetch Data from Columns and Rows of selected sheet
Dim cmd As New System.Data.OleDb.OleDbCommand("SELECT * FROM [Details$]", excelConnectionString)
Dim daCSV As New OleDbDataAdapter()
daCSV.SelectCommand = cmd
Dim dtCSV = New DataTable()
daCSV.Fill(dtCSV)
ListView1.Visible = True
ListBox2.Visible = True
'ListView1.DataBindings.Add()

Dim index As Integer
index = ComboBox1.FindStringExact(0)
Label5.Text = ("Number of Column(s) : " & dtCSV.Columns.Count)
excelConnectionString.Close()

End Sub

Here is where i have problem, the SELECT * FROM [ ], i don't wanna specify the sheet name "Details" so that i can also work on other sheet as well:

Dim cmd As New System.Data.OleDb.OleDbCommand("SELECT * FROM [Details$]", excelConnectionString)

Any solution will be appreciated.
Thanx once again

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mcmish · Accepted Answer · 2011-08-17T19:14:00

Hi All, thanx for your supports and contributions, i was able to use the

GetOleDbSchemaTable

concept to solve the problem. Found below is the solution:

VB

Dim f As New OpenFileDialog()
      f.Filter = "Excel files | *.xls"
      f.InitialDirectory = My.Application.Info.DirectoryPath

      If f.ShowDialog() = DialogResult.OK Then
          If f.FileName IsNot Nothing AndAlso f.CheckFileExists = True Then
              Me.SVTest = f.FileName
              Me.XlsPath.Text = f.FileName
              Label6.Text = ("Driver: ") & f.FileName
              Open()


              dt = ODBConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
              For Each row As DataRow In dt.Rows
                  Dim sq As New OleDbDataAdapter("select * from [" & row.Item("TABLE_NAME") & "]", ODBConn)

                  Dim XlsDT As New DataTable
                  sq.Fill(XlsDT)
                  XlsDT.TableName = row.Item("TABLE_NAME")
                  'ds.Add(XlsDT)
                  ComboBox1.Items.Add(XlsDT.TableName)

              Next
              Close()

              End If
              End If