-
Apr 28th, 2020, 05:19 PM
#1
Thread Starter
Member
[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
-
Apr 29th, 2020, 03:08 AM
#2
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:
cmd.CommandText = $"SELECT * From [{sheetname}]"
-
Apr 29th, 2020, 03:14 AM
#3
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:
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.
-
Apr 29th, 2020, 03:34 PM
#4
Thread Starter
Member
Re: VB.NET Excel Worksheets - Loop thru all worksheets in folder
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.
-
Apr 29th, 2020, 03:39 PM
#5
Thread Starter
Member
Re: VB.NET Excel Worksheets - Loop thru all worksheets in folder
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.
-
Apr 30th, 2020, 01:49 AM
#6
Re: VB.NET Excel Worksheets - Loop thru all worksheets in folder
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
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.
-
Apr 30th, 2020, 03:00 PM
#7
Thread Starter
Member
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.
-
Apr 30th, 2020, 03:08 PM
#8
Thread Starter
Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|