Paul Edward Pagente Paul Edward Pagente - 1 month ago 7
Vb.net Question

Always select the first sheet in Excel

Hello and Good Morning,

I have a piece of code here that imports an excel file and populate it in datagridview and here it is.

Dim conn As OleDbConnection

Dim dts As DataSet
Dim excel As String
Dim OpenFileDialog As New OpenFileDialog

OpenFileDialog.InitialDirectory = My.Computer.FileSystem.SpecialDirectories.MyDocuments
OpenFileDialog.Filter = "All Files (*.*)|*.*|Excel files (*.xlsx)|*.xlsx|CSV Files (*.csv)|*.csv|XLS Files (*.xls)|*xls"
If (OpenFileDialog.ShowDialog(Me) = System.Windows.Forms.DialogResult.OK) Then
Dim fi As New FileInfo(OpenFileDialog.FileName)
Dim FileName As String = OpenFileDialog.FileName
excel = fi.FullName
conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excel + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1';")
conn.Open()
Dim myTableName = conn.GetSchema("Tables").Rows(0)("TABLE_NAME")

Dim MyCommand As OleDbDataAdapter = New OleDbDataAdapter(String.Format("Select F3 as ItemCode,F4 as Description,F6 as RequestedQty,F9 as Remarks,F11 as Tag From [Official Transmittal Form$]", myTableName), conn)
dts = New DataSet
MyCommand.Fill(dts, 13, 878, "MyTable")
DataGridView1.DataSource = dts
DataGridView1.DataMember = "MyTable"
conn.Close()
' DataGridView1.Columns(7).ValueType = GetType(Double)
' DataGridView1.Columns(7).DefaultCellStyle.Format = "N2"
' DataGridView1.Columns(9).ValueType = GetType(Double)
' DataGridView1.Columns(9).DefaultCellStyle.Format = "N2"
' DataGridView1.Columns(10).ValueType = GetType(Double)
' DataGridView1.Columns(10).DefaultCellStyle.Format = "N2"
' DataGridView1.Columns(11).ValueType = GetType(Double)
' DataGridView1.Columns(11).DefaultCellStyle.Format = "N2"
' DataGridView1.Columns(12).ValueType = GetType(Double)
' DataGridView1.Columns(12).DefaultCellStyle.Format = "N2"
End If


but then my problem here is the word
Official Transmittal Form
from the code above. My Question is how can i import only the first sheet? regardless of what would be the sheet name. There must be an instances that the sheetname might change all i want is to import the first sheet. what would be the modification in my program?

I tried this code but it has an error

here is the code

Dim conn As OleDbConnection

Dim dts As DataSet
Dim excel As String
Dim OpenFileDialog As New OpenFileDialog

OpenFileDialog.InitialDirectory = My.Computer.FileSystem.SpecialDirectories.MyDocuments
OpenFileDialog.Filter = "All Files (*.*)|*.*|Excel files (*.xlsx)|*.xlsx|CSV Files (*.csv)|*.csv|XLS Files (*.xls)|*xls"
If (OpenFileDialog.ShowDialog(Me) = System.Windows.Forms.DialogResult.OK) Then
Dim fi As New FileInfo(OpenFileDialog.FileName)
Dim FileName As String = OpenFileDialog.FileName
Dim xlApp As New Microsoft.Office.Interop.Excel.Application
Dim xlWorkBook As Microsoft.Office.Interop.Excel.Application = xlApp.Workbooks.Open(OpenFileDialog.FileName)
Dim SheetName As String = xlWorkBook.Worksheets(0).Name.ToString
excel = fi.FullName
conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excel + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1';")
conn.Open()
Dim myTableName = conn.GetSchema("Tables").Rows(0)("TABLE_NAME")

Dim MyCommand As OleDbDataAdapter = New OleDbDataAdapter(String.Format("Select F3 as ItemCode,F4 as Description,F6 as RequestedQty,F9 as Remarks,F11 as Tag From [" & SheetName & "$]", myTableName), conn)
dts = New DataSet
MyCommand.Fill(dts, 13, 878, "MyTable")
DataGridView1.DataSource = dts
DataGridView1.DataMember = "MyTable"
conn.Close()
' DataGridView1.Columns(7).ValueType = GetType(Double)
' DataGridView1.Columns(7).DefaultCellStyle.Format = "N2"
' DataGridView1.Columns(9).ValueType = GetType(Double)
' DataGridView1.Columns(9).DefaultCellStyle.Format = "N2"
' DataGridView1.Columns(10).ValueType = GetType(Double)
' DataGridView1.Columns(10).DefaultCellStyle.Format = "N2"
' DataGridView1.Columns(11).ValueType = GetType(Double)
' DataGridView1.Columns(11).DefaultCellStyle.Format = "N2"
' DataGridView1.Columns(12).ValueType = GetType(Double)
' DataGridView1.Columns(12).DefaultCellStyle.Format = "N2"
End If


and here is the error

Unable to cast COM object of type 'Microsoft.Office.Interop.Excel.WorkbookClass' to interface type 'Microsoft.Office.Interop.Excel.Application'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{000208D5-0000-0000-C000-000000000046}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).

TYSM

Answer

As per the internet says, OLEDB does not preserve the index and ordering of the worksheets in Excel.

If you're that desperate, you can use this workaround:

Dim xlApp As New Microsoft.Office.Interop.Excel.Application
Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workook= xlApp.Workbooks.Open(OpenFileDialog.FileName)

Dim SheetName As String = xlWorkBook.Worksheets(1).Name.ToString

You now have the name of your first sheet then concatenate it with your query.

Dim MyCommand As OleDbDataAdapter = New OleDbDataAdapter(String.Format("Select F3 as ItemCode,F4 as Description,F6 as RequestedQty,F9 as Remarks,F11 as Tag  From [" & SheetName & "$]", myTableName), conn)