Thread: Macro for analyzing data and returning lines that meet certain criteria

You could use the following - this will allow you to browse to the file to import and then it will import only Approved quotes into the workbook. See attached workbook.

Option Explicit

Sub importQuote(fName As String, delSep As String)
Dim rdLine As String, splitStr, lRow As Long, ws1 As Worksheet
Application.ScreenUpdating = False
On Error GoTo errHandler
Set ws1 = Worksheets("Sheet1")
Open fName For Input Access Read As #1

While Not EOF(1)
    Line Input #1, rdLine
    If Left(rdLine, 5) <> "Quote" Then
        splitStr = Split(rdLine, delSep)
        If splitStr(4) = "Approved" Then
      lRow = ws1.Cells(Rows.Count, "A").End(xlUp).Row + 1
      ws1.Range("A" & lRow).Resize(, 5) = splitStr
      End If
    End If
Wend
Close #1
Application.ScreenUpdating = True
Exit Sub
errHandler:
MsgBox "Error importing data into excel - error " & Err.Number & _
" - " & Err.Description
Close #1
End Sub

Sub processFile()
Dim fName As String, delSep As String, fullFileName As String
fullFileName = Application.GetOpenFilename("CSV files (*.csv),*.csv", _
    1, "Select File to Import", , False)
If fullFileName = vbNullString Then
MsgBox "Please select a valid file to import"
End If
fName = fullFileName: delSep = vbTab
Call importQuote(fName, delSep)
End Sub

Thanks for the reply!

However, I am receiving "Error 9 - Subscript out of range" When ever I try to run the Macro.

Also, is it required that I keep CSV as the only file type, because the data I receive is already in xlsx. Could converting my data to CSV from xlsx before the import attempt be the issue?

Changed error handler line to

On Error goto 0

then used debug and it took me to this line

Set ws1 = Worksheets("Sheet1")

Can you upload a sample file that you import - I assumed it was a csv file - that is an easy fix. Did you copy my code into your own workbook - if so you need to change the line

Set ws1 = Worksheets("Sheet1")
'to the name of the sheet where the data is being imported - what is the sheet name where you import the data

Could you upload also a sample workbook that the data gets imported into - subscript out of range just means it cannot find the sheet. What sheet does the data get imported into? Upload a sample file that you import and a sample workbook that you want the data imported into. It is just a matter of referencing the sheet and workbook correctly. Sample workbooks with no sensitive data makes it easier to give you an accurate answer.