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.
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: