E
et10yl
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]
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]