Posted to microsoft.public.excel.programming
|
|
Help with SQL and ODBC Text Driver
Have a look he
http://tinyurl.com/2jm5p
RBS
"et10yl" wrote in message
...
I have a tab delimited text file (over 300 variables, so I can't stick it
straight into another Excel workbook) and I am trying to use ODBC to
connect
to the text file and pull values into an Excel file. The code I have so
far
doesn't seem to be picking up the field headers. It'll run when I take
off
the criteria, but will break when it comes to the first specific query for
a
fieldname. Here's what I have. Can someone hlep please? Thanks a lot!
'===============================================
' start: open connection string to database with data
'===============================================
Dim cn As ADODB.Connection, rs As ADODB.Recordset, strSQL As
String
'!!! This line won't work cos I have the WHERE criteria referencing
a field
strSQL = "SELECT * FROM sourcedata.txt WHERE ACE07 = " & clientCode
' !!! This line will work, but after it opens the recordset, it
doesn't seem to find any fields
' plus I need the data to be specific, so I need the WHERE clause
strSQL = "SELECT * FROM sourcedata.txt"
strFolder = "H:\Excel-Powerpoint\Current\"
Set cn = New ADODB.Connection
cn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"Dbq=" & strFolder & ";" & _
"Extensions=asc,csv,tab,txt;HDR=Yes"
If cn.State < adStateOpen Then Exit Sub
Set rs = New ADODB.Recordset
rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
'==============================================
' start: transferring values to XLS source
'==============================================
sourceWS.Range("F4").Value = rs("Female")
[...more code to transfer values to XLS]
|