musesshadow
asked on
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.FI LESYSTEMOB JECT")
strFilePath = oFSObj.GetFile(strFullPath ).ParentFo lder.Path
strFilename = oFSObj.GetFile(strFullPath ).Name
'Open an ADO connection to the folder specified
Set oConn = CreateObject("ADODB.CONNEC TION")
oConn.Open "Provider=Microsoft.Jet.OL EDB.4.0;" & _
"Data Source=" & strFilePath & ";" & _
"Extended Properties=""text;HDR=Yes; FMT=Delimi ted"""
Set oRS = CreateObject("ADODB.RECORD SET")
'Now actually open the text file and import into Excel
oRS.Open "SELECT * FROM " & strFilename, oConn, 3, 1, 1
Sheets.Add
ActiveSheet.Range("A1").Co pyFromReco rdset oRS, 65535
Wend
oRS.Close
oConn.Close
Any ideas?
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.FI
strFilePath = oFSObj.GetFile(strFullPath
strFilename = oFSObj.GetFile(strFullPath
'Open an ADO connection to the folder specified
Set oConn = CreateObject("ADODB.CONNEC
oConn.Open "Provider=Microsoft.Jet.OL
"Data Source=" & strFilePath & ";" & _
"Extended Properties=""text;HDR=Yes;
Set oRS = CreateObject("ADODB.RECORD
'Now actually open the text file and import into Excel
oRS.Open "SELECT * FROM " & strFilename, oConn, 3, 1, 1
Sheets.Add
ActiveSheet.Range("A1").Co
Wend
oRS.Close
oConn.Close
Any ideas?
ASKER
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?
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?
ASKER
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 :)
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 :)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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