Search values in multiple CSV files using VBA

kuroshfly3424

New Member
Joined
Feb 20, 2014
Messages
2
First, I'm really a rare user of Excel. And now, I need to 'develop' with it.
So, basically, I have this form in a workbook (as the first sheet). This form have the folder destination (of which the CSV files are located), the value to search for (say, a unit name), and a button. The folder destination and the value to be searched can be changed according to what the user input in the form.
When the user press the button, the macro will run, and a few of the characteristics of the matching unit in the CSV will be displayed in the next sheet of the workbook.
I have an idea of using the column to search and retrieve the value from the CSV. For example, the value to be searched for all located in the B column of the CSV, while the values need to be retrieve are in C and F respectively.
So, can anyone help me with this? I'm currently doing my research and studies on it since I'm fairly new to this language.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Hello, it would be helpful to have a real example

Hello. Thanks for the response.
This is what I have so far:

Sub Button1_Click()

Dim LSearchRow As Integer
Dim LCopyToRow As Integer
Dim strPath As String
Dim strFile As String
Dim wbSource As Workbook
Dim wsSource As Worksheet
Dim wsMain As Worksheet
Dim wsDest As Worksheet
Dim filePath
Dim numOfData
Dim unitName


On Error GoTo Err_Execute

Set wsMain = ThisWorkbook.Worksheets(1)
Set wsDest = ThisWorkbook.Worksheets(2) 'Set destination worksheet to paste the matching data

wsDest.Cells.Clear

'filePath = wsMain.Range("B4").Value 'Get the file path
unitName = wsMain.Range("B5").Value 'Get the unit name

'Open the files from the path provided
strPath = "C:\Users\Lenovo\Desktop\DataLog"
strFile = Dir(strPath & "\*.csv")

Do While strFile <> vbNullString

Set wbSource = Workbooks.Open(strPath & "\" & strFile)
Set wsSource = wbSource.Worksheets(1)

noOfData = wsSource.Range("A2:A12").Find("*", , , , xlByRows, xlPrevious).Row - 1

'Set row to start to search and copy data to
LSearchRow = 2
LCopyToRow = 2

While Len(wsSource.Range("B" & CStr(LSearchRow)).Value) > 0

'If value in column B = "unitName", copy entire row to Sheet2
If wsSource.Range("B" & CStr(LSearchRow)).Value = unitName Then

'Copy and paste the matching values into the next sheet
For Count = 2 To noOfData + 1
wsDest.Cells(1, 1).Value = "Unit No"
wsDest.Cells(1, 2).Value = unitName
wsDest.Cells(LCopyToRow, 1).Value = _
wsSource.Cells(LSearchRow, 3).Value
wsDest.Cells(LCopyToRow, 2).Value = _
wsSource.Cells(LSearchRow, 6).Value
Next Count

'Move counter to next row
LCopyToRow = LCopyToRow + 1

'Go back to Sheet1 to continue searching
wsSource.Select

End If

LSearchRow = LSearchRow + 1

Wend

wbSource.Close True
strFile = Dir()

Loop



MsgBox "All matching data has been copied."

Exit Sub

Err_Execute:
MsgBox "An error has occurred."

End Sub

The IF function (to search for matching values and copy and paste it on the next sheet) I've tested it on other file and it works just fine. I just couldn't get the read files. I have a form in excel and a button.
The B4 is the location of the folder, where 2 .csv files are located. The B5 is the unit name that I want to search in the .csv files. When it found a match, it will copy some traits or the unit name (some columns only, not the whole row) and pasted it onto the next sheet of the form.
So, now I'm stuck as to how I can get it to loop through the files in the folder and search for the value.
Any form of help would be very much appreciated.
 
Upvote 0
Did you ever get an answer to this or find a solution you could share please
 
Upvote 0

Forum statistics

Threads
1,223,898
Messages
6,175,274
Members
452,628
Latest member
dd2

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