Open Multiple Unknown Files in Separate Sheets

mobiius

New Member
Joined
Mar 30, 2011
Messages
37
Hello everyone,

Here is what I'm trying to do:
I want to open all the .csv files in a directory, but rather than open them in separate workbooks, I'd like to open each of them in a separate sheet in the same workbook. I should mention that the number of files that will be in the directory will vary from week to week.
What I would then have to do is scan through each worksheet and delete a number of specific rows. Once that is done, I will have to copy the remaining data into the main sheet so I can count the number of unique rows.

Here's some code I came across that will open all files in a directory, but it opens each one in a separate workbook.

Code:
Dim i As Integer, wb As Workbook
With Application.FileSearch
.NewSearch
.LookIn = "H:\My Documents\Spreadsheets\KB Page Access Report Generator\Test Data"
.SearchSubFolders = False
.Filename = "*.csv"
.Execute
For i = 1 To .FoundFiles.Count
'Open each workbook
Set wb = Workbooks.Open(Filename:=.FoundFiles(i))
 
'I don't need the code below so I've just commented it out for now.
 
'Perform the operation on the open workbook
'wb.Worksheets("sheet1").Range("A1") = Date
'Save and close the workbook
'wb.Save
'wb.Close
'On to the next workbook
Next i
End With
 
Last edited:

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Forum statistics

Threads
1,224,584
Messages
6,179,693
Members
452,938
Latest member
babeneker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top