About Us

Dave Peterson

Glad it worked for you!

L.Mathe wrote:

You are a Genius!! This is exactly what was needed.

I probably would have worked on this for a year a still never got it... Go
figure, I have a wb that has almost 10 pages of vba to generate 4 - 5
reports, copy data, open files, find ranges, paste, save, etc., and had
little to no problems with it. This one was beyond me!

THANK YOU!!
--
Linda

"Dave Peterson" wrote:

And there could be multiple matches in that .csv file, right???

Option Explicit
Sub GetFile()

Dim myFileNames As Variant
Dim myDestSheet As Worksheet
Dim mySearchData As String
Dim iCtr As Long

myFileNames = Application.GetOpenFilename _
(filefilter:="CSV Files,*.csv", _
MultiSelect:=True)

If IsArray(myFileNames) = False Then
Debug.Print "user cancelled"
Exit Sub 'don't keep going!
End If

Set myDestSheet = ThisWorkbook.Worksheets("Sheet1")
mySearchData = myDestSheet.Range("A1").Text

If mySearchData = "" Then
MsgBox "Nothing in the search cell!" & vbLf & "Quitting"
Exit Sub
End If

For iCtr = LBound(myFileNames) To UBound(myFileNames)
Call ReadCSV2(PassedFileName:=CStr(myFileNames(iCtr)), _
SearchData:=mySearchData, _
DestSht:=myDestSheet)
Next iCtr

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
Dim FirstAddress As String

Workbooks.OpenText Filename:=PassedFileName, _
DataType:=xlDelimited, Comma:=True

Set CSVWks = ActiveSheet

FirstAddress = ""
With CSVWks.Columns(77)
Set FoundCell = .Cells.Find(what:=SearchData, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
'we're done
Else
FirstAddress = FoundCell.Address
Do
With DestSht
NewRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
With .Cells(NewRow, "A")
.NumberFormat = "@" 'text
.Value = Left(CSVWks.Cells(FoundCell.Row, 110), 17)
End With
'filename
.Cells(NewRow, "B").Value = PassedFileName
'Row in that file
.Cells(NewRow, "C").Value = FoundCell.Row
End With

'try to find the next match
Set FoundCell = .Cells.FindNext(after:=FoundCell)
If FoundCell.Address = FirstAddress Then
Exit Do
End If
Loop
End If
End With

CSVWks.Parent.Close savechanges:=False

End Sub

L.Mathe wrote:

I would want to have the users select a file, or a group of files in a
folder. The piece of VBA you provided here seems to do exactly what I want.

Now my problem is I cannot get the 'Do' portion of my original code to work.

What needs to be accomplished is once the file(s) are selected is search
column 77 and if there is a match to what I have in Cell A1 in my worksheet,
extract the left 19 digits on the same row in Column 110. In Col. A on my
worksheet would be the 19 digit number (formatted as text) and in Col. B the
file name. Can you help??
--
Linda

"Dave Peterson" wrote:

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)

--

Dave Peterson

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
runtime error '1004' application or object defined error	Janis	Excel Programming	4	November 18th 09 03:01 PM
Run Time Error 1004: Application or Object Defined Error	BEEJAY	Excel Programming	4	October 18th 06 04:19 PM
Run Time 1004 Error: Application or Object Difine Error	BEEJAY	Excel Programming	0	October 17th 06 10:45 PM
Error 1004, Application-definded or object-defined error	Mirco Wilhelm[_2_]	Excel Programming	9	January 7th 06 04:56 PM
run-time error '1004': Application-defined or object-deifined error	[email protected]	Excel Programming	5	August 10th 05 09:39 PM

Menu

About Us