How to Loop through Excel files in a folder and extract file name and paste it into column in the files vb.net

Question

The Correct Code is as below: This code is working with vb 2019 but not working in vb 2013

If FolderBrowserDialog1.ShowDialog = 
System.Windows.Forms.DialogResult.OK Then
Dim Directory = FolderBrowserDialog1.SelectedPath
Dim Files() As System.IO.FileInfo
Dim DirInfo As New System.IO.DirectoryInfo(Directory)
Files = DirInfo.GetFiles("*", IO.SearchOption.AllDirectories)
For Each File In Files
Dim xl As New Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim Lrow As Integer
Dim Frow As Integer
Dim Crow As Integer
wb = xl.Workbooks.Open(File.FullName)
For Each ws In wb.Sheets
Dim strFileName As String = Microsoft.VisualBasic.Left(File.Name, 
(InStrRev(File.Name, ".", -1, vbTextCompare) - 1))
If ws.Name = "SCHEDULE" Then
Lrow = ws.Range("A65536").End(Excel.XlDirection.xlUp).Row
Frow = ws.Range("A:A").Find(What:="Member", After:=ws.Range("A1")).Row
Crow = Lrow - Frow
ws.Range("S1", "S" & Crow).Offset(RowNo, 0).Value2 = strFileName
End If
Next
wb.Close(SaveChanges:=vbTrue)
Next
End If

This is the Correct Code Thanks, Regards Moheb Labib

You must place `wb = xl.Workbooks.Open("File")` after `For Each File In Files` — JohnyL, May 27 '20 at 10:50
Same Error, I need to tell it that the file is a workbook ? How?? — Meho2016, May 28 '20 at 06:31
I added Option Strict On to the 1st Line of Code and still the same error — Meho2016, May 28 '20 at 10:36
@Andrew Morton The Code is working now, but I use it , it makes the excel file read only, maybe because it did not close the file, so I used wb.close before the last next, what I'm missing here also, Thank you — Meho2016, May 31 '20 at 12:08
@Meho2016 Please see [this](https://stackoverflow.com/a/53571618/1115360) for how to close Excel properly afterwards. — Andrew Morton, May 31 '20 at 12:21

How to Loop through Excel files in a folder and extract file name and paste it into column in the files vb.net

0 Answers0