Reading "*xls" File in a Folder via vb.net

Help Studio
,
1

Hi guys,

I need to reading a *"xls" files in a folder via vb. net.

How can i do that ?

NOT : I’m not open it just listing in a console
→ ExcelFolder → abc.xls
→ ExcelFolder → bbb.xls
→ ExcelFolder ->ccc.xls

Thanks.

2

You can use excel activities to read the excel files. What is the reason you want to read via Vb.NET ?

1 Like
3

Hi @Kuki_Force

Use a for each file in folder and use read range from excel activities

If this is what you are looking for

Or if you need only specific invb.net then may I know why it is?

If you still need only in code then here is where you can check

Cheers

1 Like
4

hi,

in my process, i used vb code. I cannot use activity.
But i can learn both ways with via code and activities.

Thank you.

5

@AkshaySandhu , @Anil_G

I found the solution. The code works fine but doesn’t listing in excel files. but listing subfolders and inside of the excel files.
I mean lets say, we have Todays Folder and includes 2 folder and 5 excel file inside of it.
What i want is that => listing that 5 excel files. but the code is listing excel files of inside of the that 2 folders.

    getFolders = Directory.GetDirectories(FolderLocation)
	Console.WriteLine("Number of Folder : " + getFolders.Count.ToString)
	getFiles = Directory.GetFiles(FolderLocation)
	Console.WriteLine("Number of Files : " + getFiles.Count.ToString)
	
	For Each Folder As String In getFolder
		lst_KeepFolder.Add(Folder)
 		Console.WriteLine("Folder : " + lst_KeepFolder.Item(myCounter))
		Excels = Directory.GetFiles(lst_KeepFolder.Item(myCounter).ToString,"*xls")
		For Each Files As String In Excels
			lst_KeepExcelDoc.Add(Files)
			Console.WriteLine(lst_KeepFolder.Item(myCounter).ToString + " -> " + Files.Substring(36)) 'I have an excel files but does not listing.
		Next
	Next

Thank you

6

Hi @Kuki_Force

Please remove for folder in folder. You do not need that. That is what is making the files inside the folder to be looped

getFiles = Directory.GetFiles(FolderLocation,"*xls")
Console.WriteLine("Number of Files : " + getFiles.Count.ToString)

	For Each Files As String In getFiles
		lst_KeepExcelDoc.Add(Files)
Next

Cheers

1 Like
7

Just modify your code to look something like below
Directory.GetFiles(FolderLocation, "*.xlsx", SearchOption.AllDirectories)

1 Like
8

Just incase you are looking for .NET code then you can use the below

dt_Temp = Read_Range("H:\Temp\New Microsoft Excel Worksheet.xlsx")
dt_Temp2 = Read_Range("H:\Temp\New Microsoft Excel Worksheet_2.xlsx")
'Do you coding here
End Sub

Public Function Read_Range(ExcelFilePath As String) As DataTable 
Dim conn As System.Data.OleDb.OleDbConnection
Dim dtr As System.Data.OleDb.OleDbDataReader
Dim dta As System.Data.OleDb.OleDbDataAdapter
Dim cmd As System.Data.OleDb.OleDbCommand
Dim dts As DataSet
conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelFilePath + ";Extended Properties=Excel 12.0;")
dta = New OleDbDataAdapter("Select * From [Sheet1$]", conn)
dts = New DataSet
dta.Fill(dts, "[Sheet1$]")
Read_Range = dts.Tables(0)
conn.close
End Function

Sub Test()

image
image847×500 24.5 KB

1 Like
9

Thanks for answer and effort. :slight_smile:

10

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.