Excel/VBA ADO Query

Hi All,

I have a series of log files I'm trying to organize into excel by treating the text logs as an ADO Recordset.

The format of the logs is as follows:

"Column1" "Column2" "Column3" "Column4"...
"Data1" "Data2" "Data3" "Data4"...

When I query "Select * from "... it only seems to be returning the first column, not the entire row as desired...

The code follows:

'This gives us a full path name e.g. C:tempfolderfile.txt
          'We need to split this into path and file name
          Set oFSObj = CreateObject("SCRIPTING.FILESYSTEMOBJECT")

          strFilePath = oFSObj.GetFile(strFullPath).ParentFolder.Path
          strFilename = oFSObj.GetFile(strFullPath).Name


          'Open an ADO connection to the folder specified
          Set oConn = CreateObject("ADODB.CONNECTION")
          oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                     "Data Source=" & strFilePath & ";" & _
                     "Extended Properties=""text;HDR=Yes;FMT=Delimited"""

          Set oRS = CreateObject("ADODB.RECORDSET")

          'Now actually open the text file and import into Excel
          oRS.Open "SELECT * FROM " & strFilename, oConn, 3, 1, 1    
              Sheets.Add
             ActiveSheet.Range("A1").CopyFromRecordset oRS, 65535
          Wend

          oRS.Close
          oConn.Close

Any ideas?
musesshadowAsked:
Who is Participating?
 
ee_ai_constructConnect With a Mentor Commented:
PAQ / Refund
ee ai construct, community support moderator
0
 
cdemirCommented:
pseudo code :

rownum = 1
while not rs.eof
   colnum = 1
   cells(rownum,colnum) = rs.fields(0)
   colnum = 2
   cells(rownum,colnum) = rs.fields(1)
   ...
   rownum = rownum + 1
wend
0
 
musesshadowAuthor Commented:
Hi CDemir,

I had thought of this too, but it doesn't seem to work.
Whether I try the standard reader (as in my initial post) or the .fields(x) option, I can only seem to get information from the file for the first column of information.

Would this be because of the quotes and the spacing?  Is the delimitation of the file screwing excel?
0
 
musesshadowAuthor Commented:
A standard "file reader"

Open sFileName For Input Access Read As #1

          While Not EOF(1)
             Line Input #1, WholeLine
             Cells(RowNdx, ColNdx).Value = WholeLine
             RowNdx = RowNdx + 1
          Wend
          Close #1

Seems to work.

Please close case with me as the solution :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.