Loop Through a Folder of Excel Workbooks in VB.NET

[kvenkatu]

Hi,
Can anyone tell me how to Loop Through a Folder of Excel Workbooks
using VB.Net?
I have a folder with Excel files. Each spreadsheet has multiple
worksheets.
I need to read each worksheet and get the data and transfer that data
to a database table.
I am not sure how to loop through Excel workbooks and get the worksheet
name.

I would appreciate any help with this.

Thank you.

 

[John T]

Import these libraries:

Imports System.IO
Imports Microsoft.Office.Interop

Then in your method body:

Dim objDirectory As Directory
Dim arrFileNames As String() = objDirectory.GetFiles("X:\Excel
Workbooks")
Dim strFileName As String
Dim objExcelApplication As New Excel.Application
Dim objExcelWorkbook As Excel.Workbook
Dim objExcelWorksheet As Excel.Worksheet

For Each strFileName In arrFileNames
objExcelWorkbook =
objExcelApplication.Workbooks.Open(strFileName)
For Each objExcelWorksheet In objExcelWorkbook.Worksheets
' Do something with objExcelWorksheet.Name
Next
objExcelWorkbook.Close()
Next

objExcelApplication = Nothing