Does this mean you want to process all the files in the folder or does this mean
you want to have the user select the files in the folder?
If you want to select multiple (but not necessarily all), you can change this:
Option Explicit
Sub GetFile()
Dim myFileName As Variant
Dim myDestSheet As Worksheet
Dim mySearchData As String
dim iCtr as long
myFileName = Application.GetOpenFilename(multiselect:=true)
If isarray(myFileName) = False Then
Debug.Print "user cancelled"
Exit Sub 'don't keep going!
End If
Set myDestSheet = ThisWorkbook.Worksheets("Sheet1")
mySearchData = myDestSheet.Range("A1").Text
for ictr = lbound(myfilename) to ubound(myfilename)
Call ReadCSV2(PassedFileName:=CStr(myFileName(ictr)), _
SearchData:=mySearchData, _
DestSht:=myDestSheet)
next ictr
End Sub
If you want to open all the .csv files, then look at Ron de Bruin's site. He
has different ways to approach it--including combining all the .csv files into
one and just processing it once.
http://www.rondebruin.nl/txtcsv.htm
or
http://www.rondebruin.nl/csv.htm
L.Mathe wrote:
My apologies that I did not include the entire routine... once the CSV file
is open, it has to search Column 77, and if found, extract the data from the
same row in column 110 and and the left 19 digits from column 70. So the
complete sub-routine following "check if data exists in column 77" is:
Set c = CSVSht.Columns(77).Find(What:=SearchData, _
LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then
FirstAddr = c.Address
Do
Data1 = CSVSht.Cells(c.Row, 110)
Data2 = CSVSht.Cells(c.Row, 70)
Data3 = Left(Data2, 19)
With ThisWorkbook.Sheets(DestSht)
.Range("B" & RowCount) = FName
.Range("A" & RowCount) = Data3
RowCount = RowCount + 1
End With
Set c = CSVSht.Columns(77).FindNext(after:=c)
Loop While Not c Is Nothing And c.Address < FirstAddr
End If
CSVFile.Close savechanges:=False
FName = Dir()
Loop
Application.ScreenUpdating = False
Range("A3:B500").Select
Selection.Sort Key1:=Range("B3"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A3").Select
Application.ScreenUpdating = False
MsgBox "Search Is Complete", vbInformation
End Sub
Again, I was trying to modify vba I had where an entire folder (containing
at least 30 files) is selected and extracting the data.
Thank you!
--
Linda
"Dave Peterson" wrote:
First, your code continues even when the user doesn't select a file to open:
Sub GetFile()
Dim FileName As Variant
FileName = Application.GetOpenFilename
If FileName = False Then
Debug.Print "user cancelled"
exit sub ' don't keep going!
Else
Debug.Print "file selected: " & FileName
End If
Call ReadCSV2(myFileName, SearchData, DestSht)
End Sub
Second, the stuff you're passing to the called procedure isn't used the way you
wrote it.
This may get you closer, but it's not complete. That "etc" didn't give me any
idea what should be done.
Option Explicit
Sub GetFile()
Dim myFileName As Variant
Dim myDestSheet As Worksheet
Dim mySearchData As String
myFileName = Application.GetOpenFilename
If myFileName = False Then
Debug.Print "user cancelled"
Exit Sub 'don't keep going!
End If
Debug.Print "file selected: " & myFileName
Set myDestSheet = ThisWorkbook.Worksheets("Sheet1")
mySearchData = myDestSheet.Range("A1").Text
Call ReadCSV2(PassedFileName:=CStr(myFileName), _
SearchData:=mySearchData, _
DestSht:=myDestSheet)
End Sub
Sub ReadCSV2(PassedFileName As String, _
SearchData As String, _
DestSht As Worksheet)
Dim CSVWks As Worksheet
Dim FoundCell As Range
Dim NewRow As Long
With DestSht
NewRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
End With
Workbooks.OpenText FileName:=PassedFileName, _
DataType:=xlDelimited, Comma:=True
Set CSVWks = ActiveSheet
'check if data exists in column 77
With CSVWks.Columns(77)
Set FoundCell = .Cells.Find(what:=SearchData, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With
End Sub
L.Mathe wrote:
Using Excel 2003. I'm trying to use VBA to select & open a csv file, search
for specific text (using the data in Cell A1 of my wb), etc. However, I am
getting a Run-Time error 1004. Where am I going wrong? The code in part is
as follows:
Sub GetFile()
Dim FileName As Variant
FileName = Application.GetOpenFilename
If FileName = False Then
Debug.Print "user cancelled"
Else
Debug.Print "file selected: " & FileName
End If
Call ReadCSV2(myFileName, SearchData, DestSht)
End Sub
Sub ReadCSV2(ByVal myFileName, ByVal SearchData As String, ByVal DestSht)
DestSht = "sheet1"
With ThisWorkbook.Sheets(DestSht)
SearchData = .Range("A1").Text
End With
Dim Data As String
Dim Data1 As Date
Dim Data2 As String
Dim Data3 As String
LastRow = ThisWorkbook.Sheets(DestSht) _
.Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
RowCount = NewRow
FName = "Temp"
Do While FName < ""
Workbooks.OpenText FileName:="Temp", DataType:=xlDelimited, Comma:=True
Set CSVFile = ActiveWorkbook
Set CSVSht = CSVFile.Sheets(1)
'check if data exists in column 77
Set c = CSVSht.Columns(77).Find(What:=SearchData, _
LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then
FirstAddr = c.Address ,,,,,,etc
I get the error code when it hits the line:
Workbooks.OpenText FileName:="Temp", DataType:=xlDelimited, Comma:=True
Can someone please help, I'm getting frustrated!
Thanks
--
Linda
--
Dave Peterson
.
--
Dave Peterson