Results 1 to 8 of 8

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

  1. #1

    Thread Starter
    Member
    Join Date
    Jul 2016
    Posts
    56

    Resolved [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

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: VB.NET Excel Worksheets - Loop thru all worksheets in folder

    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:
    1. cmd.CommandText = $"SELECT * From [{sheetname}]"

  3. #3
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: VB.NET Excel Worksheets - Loop thru all worksheets in folder

    As for the issue, you can get all paths of files in a folder by calling Directory.GetFiles. This code:
    vb.net Code:
    1. 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.

  4. #4

    Thread Starter
    Member
    Join Date
    Jul 2016
    Posts
    56

    Re: VB.NET Excel Worksheets - Loop thru all worksheets in folder

    Quote Originally Posted by jmcilhinney View Post
    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:
    1. 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.

  5. #5

    Thread Starter
    Member
    Join Date
    Jul 2016
    Posts
    56

    Re: VB.NET Excel Worksheets - Loop thru all worksheets in folder

    Quote Originally Posted by jmcilhinney View Post
    As for the issue, you can get all paths of files in a folder by calling Directory.GetFiles. This code:
    vb.net Code:
    1. 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.

  6. #6
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: VB.NET Excel Worksheets - Loop thru all worksheets in folder

    Quote Originally Posted by AngelSpeaks View Post
    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
    Last edited by ChrisE; Apr 30th, 2020 at 02:08 AM.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  7. #7

    Thread Starter
    Member
    Join Date
    Jul 2016
    Posts
    56

    Re: VB.NET Excel Worksheets - Loop thru all worksheets in folder

    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.

  8. #8

    Thread Starter
    Member
    Join Date
    Jul 2016
    Posts
    56

    Re: VB.NET Excel Worksheets - Loop thru all worksheets in folder

    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width