Error 1004-application or object error

[L.Mathe]

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

 

[Dave Peterson]

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]

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!

 

[KC]

As I said earlier, ThisWorkBook is the workbook the code sits. So you have
searchdata in sheet1.range("A1")

Now the whole of GetFile is not required.

In ReadCSV2, you never declared any parameters to start with. Probably it
will run when called.
after the first 4 lines, you have
myFileName=Nothing
SearchData=ThisWorkBook.Sheets("sheet1").range("A1").text

Then you declared FName="Temp"
So the CSV file you try to open MUST be "Temp", NOT Temp.csv or anything
else.

If you do have a csv file named Temp (without any extension), your code may
work. AND you do not need Dir later to loop.

VBA is easy only when you understand it. Getting frustrated at start, is
normal.

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!

 

[Dave Peterson]

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

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!

 

[L.Mathe]

Thanks for the explanation. Sorry for my 'frustration', I've been trying to
work this one out for about 4 days now!

 

[L.Mathe]

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??

 

[Dave Peterson]

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

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??

 

[L.Mathe]

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!!

 

[Dave Peterson]

Glad it worked for you!

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!!