Advice on Text file query

[hrm1220]

Hello, I'm hoping someone can point me in the right direction on the best method for pulling in data from a text file to excel 2003.

The text file can have as many as 1Million rows.

so I've been trying to do the data query in Excel (I've already set up an ODBC connection for txt,csv, etc.)
The issue that I've been having with this is that the first row is the name of the report and the 2nd row is the header, so I don't know how to have the query look at the 2nd row.

Then I thought I would open the text file and then loop through it to pull in the data that I need (which means I would have to loop through 1Million rows up to 10 times, depending on the users inputs). But I'm afraid I don't know too much about text files and how to code it using VBA for excel.

Any input would be appreciated.

 

[SkipVought]

How about opening the text file(s) to delete the record containing the filename.

Now your first record will be headings.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[hrm1220]

thx for the input skip, is there a way to do this through code, since the user wouldn't be opening up the text file, but the code will?

 

[SkipVought]

Check out the OPEN statement (Visual Basic for Applications) in VBA help (not the Open Method).

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[hrm1220]

so I've fixed the removal of the first row, but now I'm getting an error message:
run-time error '-2147467259 (80004005)' microsoft jet database engine cannot open the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view its data

The text file is closed (no other person has it open),I'm the one that created the text file that it's trying to pull in, I have access to the network drive and the correct permissions, so I'm confused on why my code breaks. I also made sure the Microsoft ActiveX Data Objects 2.8 Library is selcted

Sub TEST()
    Dim l_rsTest As New ADODB.Recordset
    Dim l_lRow As Long
    Dim l_lColumn As Long
    Dim l_iCounter As Integer
    Dim l_sConnection As String
    

strfilename= "\\test405\test\dogs\"
    sfilename = "test_8_20-8_27_2006"
   Set m_cnTextFile = New ADODB.Connection

  l_sConnection = "Provider=MSDASQL;Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=" & strfilename & ";" & _
                     "Extended Properties='text;HDR=no;FMT=Delimited'"  '"Initial Catalog=" & sfilename &
    
    'This code opens the connection.
  

    With m_cnTextFile
        .ConnectionString = l_sConnection
        .ConnectionTimeout = 0
        .Open
    End With
    strSQL = "Select * from [" & sfilename & "]"
     [b] l_rsTest.Open strSQL, m_cnTextFile, adOpenStatic, adLockReadOnly, adCmdText[/b]
    l_lRow = 1
    
    'Add field names to Row 1
    For l_lColumn = 0 To l_rsTest.Fields.Count - 1
         ThisWorkbook.Worksheets("Sheet1").Cells(l_lRow, l_lColumn + 1) = l_rsTest.Fields(l_lColumn).Name
    Next l_lColumn
    l_lRow = l_lRow + 1
    
    'Add the records to the worksheet
    ' this needs to meet a certain criteria before it can pull in the data to Excel 2003
    Do Until l_rsTest.EOF
        For l_lColumn = 0 To l_rsTest.Fields.Count - 1
             ThisWorkbook.Worksheets("Sheet1").Cells(l_lRow, l_lColumn + 1) = l_rsTest.Fields(l_lColumn).Value
        Next l_lColumn
        
        l_lRow = l_lRow + 1
        l_rsTest.MoveNext
    Loop
    
    'Clean up objects
    l_rsTest.Close
    Set l_rsTest = Nothing
    m_cnTextFile.Close
    Set m_cnTextFile = Nothing
    
End Sub

Please keep in mind I'm completely new to the code for text files and ODBC connections.

Also I'm not sure if I'm telling the connection string that the text file is a Tab delimited any help on this as well would be greate

So any help would be great