[RESOLVED] VB.NET Excel Worksheets - Loop thru all worksheets in folder

Currently, my VB.NET 2019 application has a form to select a workbook, then the sheet, and then load the worksheet to a datagrid and then add to a database table (Access). This works great for a couple of workbooks.

On a monthly basis, I have 102 worksheets to import. The worksheets are all created from a PDF to worksheet application, so the sheet name is always the same.

My search results only come up with VBA suggestions.

Thanks for any assistance.

Code to process sheet:

Code:

---


    Private Sub BtnSheet_Click(sender As Object, e As EventArgs) Handles btnSheet.Click
        ExcelFile = txtFileName.Text
        Dim extension As String = Path.GetExtension(ExcelFile)
        Dim filePath As String = ExcelFile
        Dim header As String = "YES"
        fileName = Path.GetFileNameWithoutExtension(ExcelFile)
        ClearDGV()
        prbStatus.Refresh()
        sheetname = cboSheet.SelectedValue.ToString
        conStr = String.Empty
        Select Case extension
            Case ".xls"
                'Excel 97-03
                conStr = String.Format(Excel03ConString, filePath, header)
                Exit Select
            Case ".xlsx"
                'Excel 07
                conStr = String.Format(Excel07ConString, filePath, header)
                Exit Select
        End Select
        Using con As New OleDb.OleDbConnection(conStr)
            Using cmd As New OleDbCommand()
                Using oda As New OleDbDataAdapter()
                    cmd.CommandText = (Convert.ToString("SELECT * From [") & sheetname) + "]"
                    cmd.Connection = con
                    con.Open()
                    oda.SelectCommand = cmd
                    oda.Fill(dtable)
                    con.Close()
                  dgvRatesAll.DataSource = dtable
                    btnImport.Enabled = True
                End Using
            End Using
        End Using
    End Sub

---

Code:

---

cmd.CommandText = (Convert.ToString("SELECT * From [") & sheetname) + "]"

---

Why are you converting a String to a String and also using two different concatenation operators in the one expression? I suggest that you use string interpolation for simplicity and clarity:

vb.net Code:

cmd.CommandText = $"SELECT * From [{sheetname}]"

As for the issue, you can get all paths of files in a folder by calling Directory.GetFiles. This code:

vb.net Code:

Dim filePaths = Directory.GetFiles(folderPath, "*.xls")

will actually get paths for XLS and XLSX files, as a three-character extension matches extensions of any length that start with those three characters.

As for getting the sheets in a workbook, I'm not 100% sure. You can definitely do it using Excel Automation, but it would be nice to avoid that if possible. You can try the GetSchema and GetOleDbSchemaTable methods and at least one of them will probably get you the information you want. I've used it with genuine databases but never an Excel workbook so I'm not sure. I would expect at least the latter to work though.

Quote:

---

Originally Posted by jmcilhinney

Code:

---

cmd.CommandText = (Convert.ToString("SELECT * From [") & sheetname) + "]"

---

Why are you converting a String to a String and also using two different concatenation operators in the one expression? I suggest that you use string interpolation for simplicity and clarity:

vb.net Code:

cmd.CommandText = $"SELECT * From [{sheetname}]"

---

Thanks for the suggestion. Why? Good question. When I googled for suggestions on importing Excel spreadsheets, I found that code and copied in verbatim. I will use the code you suggested.

Quote:

---

Originally Posted by jmcilhinney

As for the issue, you can get all paths of files in a folder by calling Directory.GetFiles. This code:

vb.net Code:

Dim filePaths = Directory.GetFiles(folderPath, "*.xls")

will actually get paths for XLS and XLSX files, as a three-character extension matches extensions of any length that start with those three characters.

As for getting the sheets in a workbook, I'm not 100% sure. You can definitely do it using Excel Automation, but it would be nice to avoid that if possible. You can try the GetSchema and GetOleDbSchemaTable methods and at least one of them will probably get you the information you want. I've used it with genuine databases but never an Excel workbook so I'm not sure. I would expect at least the latter to work though.

---

Thanks. I appreciate your help.

Quote:

---

Originally Posted by AngelSpeaks

Currently, my VB.NET 2019 application has a form to select a workbook, then the sheet, and then load the worksheet to a datagrid and then add to a database table (Access). This works great for a couple of workbooks.

[/CODE]

---

that is one step to much, unless you want to look at the Data in the DGV

this will create a new Table in a Access Database from an Excel sheet

Code:

---

Option Strict On

Imports System.Data.OleDb


Public Class Form2

 

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click

        Dim sSql As String
        Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\database1.accdb;Persist Security Info=False")
        Dim Filename As String

        Filename = "E:\Berichte\TestExcel.xlsx"

      'Export to Excel
        'sSql = "Select * " & _
        ' "Into [EmployeeList] In '" & Filename & "' 'EXCEL 12.0 XML;' " & _
        ' "From Kunden Order By Firma"

        '//Import from Excel to new Table
        sSql = "SELECT * INTO [;DATABASE=E:\database1.accdb].NewTableFromExcel From [Sheet3$] In " & _
                              "'E:\vbExcel.xlsx' 'EXCEL 12.0 XML;' "

        'take a look at your Sql
        MsgBox(sSql)

        con.Open()
        ExecuteSQL(con, sSql)
        con.Close()
        con = Nothing
      ' Process.Start(Filename) 'start and show Excel file
    End Sub

    Public Function ExecuteSQL(ByVal Con As OleDb.OleDbConnection, _
                                ByVal sSQL As String, _
                                Optional ByRef ErrMessage As String = Nothing, _
                                Optional ByVal TransAction As  _
                                OleDb.OleDbTransaction = Nothing) As Integer
        ErrMessage = Nothing
        Try
            Dim Result As Integer = 0
            Using Cmd As New OleDb.OleDbCommand(sSQL, Con, TransAction)
                Result = Cmd.ExecuteNonQuery
            End Using
            Return Result
        Catch ex As Exception
            ErrMessage = ex.Message
            Return 0
        End Try
    End Function

End Class

---

hth

Thanks for the suggestion Chris, but I have to do edits before I bring the spreadsheet into a table. I will save your idea for later.

OK, here is my code incase anyone wants to use it. I am bringing in all Excel spreadsheets in a selected folder into a datagrid, manipulating the data, and adding it to a table.

First code snippet:

Code:

---

Imports System.IO
Imports System.Data.OleDb
Imports System.Data
Imports Microsoft.Office.Interop.Excel

---

Loop thru and import all spreadsheets:

Code:

---

Private Sub BtnImport_Click(sender As Object, e As EventArgs) Handles btnImport.Click
        Dim Directory As New IO.DirectoryInfo(txtFolderName.Text)
        Dim allFiles As IO.FileInfo() = Directory.GetFiles("*.xls")
        Dim singleFile As IO.FileInfo
        For Each singleFile In allFiles
            lblMessage.Text = (singleFile.FullName)
            ExcelFile = singleFile.FullName
            LoadSpreadsheet()
            UpdateRatesAll()
        Next
    End Sub

---

Code:

---

Private Sub LoadSpreadsheet()
        Dim filePath As String = ExcelFile
        connString = String.Empty
        If filePath.EndsWith(".xlsx") Then            '2007 Format           
            connString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=No,IMEX=1'", filePath)
        Else            '2003 Format           
            connString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=No,IMEX=1'", filePath)
        End If       
        Dim connExcel As New OleDb.OleDbConnection(connString)
        Dim cmdExcel As New OleDbCommand()
        Dim oda As New OleDbDataAdapter()
        cmdExcel.Connection = connExcel
        connExcel.Open()
        cboSheet.DataSource = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
        cboSheet.DisplayMember = "TABLE_NAME"
        cboSheet.ValueMember = "TABLE_NAME"
        connExcel.Close()
        ProcessSheet()
    End Sub

---

Code:

---

Private Sub ProcessSheet()
        Dim extension As String = Path.GetExtension(ExcelFile)
        Dim filePath As String = ExcelFile
        Dim header As String = "YES"
        fileName = Path.GetFileNameWithoutExtension(ExcelFile)
        ClearDGV()
        prbStatus.Refresh()
        sheetname = cboSheet.SelectedValue.ToString
        conStr = String.Empty
        Select Case extension
            Case ".xls"
                conStr = String.Format(Excel03ConString, filePath, header)
                Exit Select
            Case ".xlsx"
                conStr = String.Format(Excel07ConString, filePath, header)
                Exit Select
        End Select
        Using con As New OleDb.OleDbConnection(conStr)
            Using cmd As New OleDbCommand()
                Using oda As New OleDbDataAdapter()
                    cmd.CommandText = $"SELECT * From [{sheetname}]"
                    cmd.Connection = con
                    con.Open()
                    oda.SelectCommand = cmd
                    oda.Fill(dtable)
                    con.Close()
                    dgvRatesAll.DataSource = dtable
                End Using
            End Using
        End Using
    End Sub

---

UpdateRatesAll is just the edits and updates.

HTH. Thanks for the suggestions.