Biboy Biboy - 19 days ago 7
Vb.net Question

How to read Sheet of an excel file regardless if it's renamed?

What I'm trying to do is to a converter. I'm having issues into opening an excel file since it's specified as [Sheet1$]. If the sheet has been renamed, my code is not reading it. Is there a way for me to get any name of Sheet perhaps?

Private Sub btnSelect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSelect.Click
If cmbPortal.Text.Length = 0 Then
MessageBox.Show("Select portal to process first", "Invalid", MessageBoxButtons.OK, MessageBoxIcon.Warning)
Else
Select Case cmbPortal.Text
Case "Robinsons"
FileFilter = "Portable Document File (*.pdf)|*.pdf"
txtFilePath.Text = parse.showdialog(FileFilter)
If txtFilePath.Text = Nothing Then
MessageBox.Show("No file was selected", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information)
End If
Case "Waltermart"
FileFilter = "Portable Document File (*.pdf)|*.pdf"
txtFilePath.Text = parse.showdialog(FileFilter)
If txtFilePath.Text = Nothing Then
MessageBox.Show("No file was selected", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information)
End If
Case "7-11"
Dim OpenFileDialog1 As New OpenFileDialog
With OpenFileDialog1
.FileName = "Excel File"
.Title = "Open File"
.InitialDirectory = "C:\User\Desktop"
.Filter = "EXCEL|*.xls;*.xlsx;*.xlsm;*.xlsb"
.FilterIndex = 1
End With
If OpenFileDialog1.ShowDialog(Me) = System.Windows.Forms.DialogResult.OK Then
Me.Cursor = Cursors.WaitCursor
Try
Dim FileName As String = OpenFileDialog1.FileName
Dim strConnString As String
strConnString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & FileName & ";"
Dim strSQL As String
strSQL = "SELECT * FROM [Sheet1$]"
Dim y As New Odbc.OdbcDataAdapter(strSQL, strConnString)
y.Fill(RetVal)
txtFilePath.Text = FileName
Catch
MessageBox.Show("An Error Occurred, Please Contact MIS for Assistance", "SYSTEM", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
Me.Cursor = Cursors.Default
ElseIf txtFilePath.Text = Nothing Then
MessageBox.Show("No file was selected", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information)
End If
Me.Cursor = Cursors.Default
End Select
If txtFilePath.Text.Length = 0 Then
btnSelect.Enabled = True
btnConvert.Enabled = False
cmbPortal.Enabled = True
Else
btnSelect.Enabled = False
btnConvert.Enabled = True
cmbPortal.Enabled = False
End If
End If
End Sub

Answer

Try this:

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

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

Now that you have the sheet name, you can concatenate it to your strSQL like this one:

strSQL = "SELECT * FROM [" & SheetName & "$]"

This will give you the first worksheet in the workbook.

Comments