Macro to select

  • Thread starter Thread starter N! Xau
  • Start date Start date
N

N! Xau

Hi,

I need to import a .csv file, selecting record depending on a certain field.
E.g., a row in the file looks like:
"1234","abcd","20040302","tom"

and I want the macro to import _only_ the rows having "tom" in the 4th
column.
Can anybody help me? Thanks
 
One way:



Public Sub InputTom()
Dim vIn (1 To 1, 1 To 4)
Dim nFileNum As Long
Dim rDest As Range
Set rDest = Sheets("Sht1").Range("A1").Resize(1, UBound(vIn, 2))
nFileNum = FreeFile
Open "Test.txt" For Input As #nFileNum
Do While Not EOF(nFileNum)
Input #nFileNum, vIn(1, 1), vIn(1, 2), vIn(1, 3), vIn(1, 4)
If vIn(1, 4) = "Tom" Then
With rDest
.Value = vIn
Set rDest = .Offset(1, 0)
End With
End If
Loop
End Sub
 
JE McGimpsey said:
One way:



Public Sub InputTom()
Dim vIn (1 To 1, 1 To 4)
Dim nFileNum As Long
Dim rDest As Range
Set rDest = Sheets("Sht1").Range("A1").Resize(1, UBound(vIn, 2))
nFileNum = FreeFile
Open "Test.txt" For Input As #nFileNum
Do While Not EOF(nFileNum)
Input #nFileNum, vIn(1, 1), vIn(1, 2), vIn(1, 3), vIn(1, 4)
If vIn(1, 4) = "Tom" Then
With rDest
.Value = vIn
Set rDest = .Offset(1, 0)
End With
End If
Loop
End Sub
Click to expand...

Thanks JE, the file has actually 10 columns, I changed the dim intruction in
dim(1 to 1, 1 to 10) and the input line adding vIn(1,5) ... vIn(1,10) .
I wrote my filename in "test.txt".
I get a subscript out of range error. I think the dim should be different.

thanks
 
More likely your sheet isn't named "Sht1". Change that to the sheet
you're trying to import to.
 
JE McGimpsey said:
More likely your sheet isn't named "Sht1". Change that to the sheet
you're trying to import to.
Click to expand...

ok, I still have some troubles
I get an error: "Input past end of file". Here is the macro as it is now:

Public Sub img()
Dim vIn(1 To 1, 1 To 10)
Dim nFileNum As Long
Dim rDest As Range
Set rDest = Sheets("Sheet1").Range("A1").Resize(1, UBound(vIn, 2))
nFileNum = FreeFile
Open "C:\UPS\ups.csv" For Input As #nFileNum
Do While Not EOF(nFileNum)
Input #nFileNum, vIn(1, 1), vIn(1, 2), vIn(1, 3), vIn(1, 4),
vIn(1, 5), vIn(1, 6), vIn(1, 7), vIn(1, 8), vIn(1, 9), vIn(1, 10)
If vIn(1, 9) = "PARAM1" or vIn(1, 9) = "PARAM2" or vIn(1, 9) =
"PARAM3" or vIn(1, 9) = "PARAM4" Then
With rDest
.Value = vIn
Set rDest = .Offset(1, 0)
End With
End If
Loop
End Sub


actually, this is the situation:
- I need to import rows based on 4 (or more) different parameters,
- I'd like to have the selections in 4 different sheets
- I'd like to have an autofit of the columns after data is imported
- the file .csv to import has a header row, I'd like to import it too, but
it's not very important

thanks a lot for your help
 
Back
Top