Solved

Excel/VBA ADO Query

Posted on 2006-07-21
5
431 Views
Last Modified: 2011-10-03
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?
0
Comment
Question by:musesshadow
  • 2
5 Comments
 
LVL 3

Expert Comment

by:cdemir
ID: 17174471
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
 

Author Comment

by:musesshadow
ID: 17178523
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
 

Author Comment

by:musesshadow
ID: 17178706
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
 

Accepted Solution

by:
ee_ai_construct earned 0 total points
ID: 17405208
PAQ / Refund
ee ai construct, community support moderator
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Web Application Architecture 6 75
word0 challenge 3 78
Not needed 13 94
Powershell Script need assistance to make some changes 4 62
How to remove superseded packages in windows w60 or w61 installation media (.wim) or online system to prevent unnecessary space. w60 means Windows Vista or Windows Server 2008. w61 means Windows 7 or Windows Server 2008 R2. There are various …
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now