Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with SQL and ODBC Text Driver
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] |
#2
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] |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel ODBC Driver with ADO | Excel Programming | |||
ODBC Driver/Import Data | Excel Discussion (Misc queries) | |||
xl odbc driver, Excel 2003 | Setting up and Configuration of Excel | |||
which odbc excel driver is the best? | Excel Programming | |||
ODBC driver file path | Excel Programming |