Just started a new job and they have a legacy DOS app outputting data in CSV form. I've take over a small project to do report writing based on this CSV file.
It is required in Excel format.
I'm using vba to find and open the CSV and saving it as excel. That is ok. I'm wanting to write sql queries to sort the data as the users have virtually no excel experience so everything has to automated.
The problem ( yes i've finally got to asking it) is. How the hell do i get the ado to give me a list of field names as variables so i can get it to query the data.
Here's my basic code.
Sub DSN_Connection()
'Create ODBC DSN Connection
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
With cn
.Provider = "MSDASQL"
.ConnectionString = "DSN=MyExcelDSN;"
.Open
End With
'get data using SQL
Dim Rs As New ADODB.Recordset
'sql
Rs.Open "Select * from [Test$]", cn, CursorType:=adOpenStatic, LockType:=adLockReadOnly
'*****************************************
HERE'S THE PROBLEM
'*****************************************
Dim objFields, fields(), i
i = 1
ReDim fields(Rs.fields.Count)
For Each objFields In Rs.fields
fields(i) = Rs.Fields.name
i = i + 1
Next
'*****************************************
'populate combo box with records from a field
UserForm2.ComboBox1.Clear
Do While Not Rs.EOF
UserForm2.ComboBox1.AddItem Rs.fields("field1"
Rs.MoveNext
Loop
'Clear connection
Rs.Close
cn.Close
Set Rs = Nothing
'Show form with data loaded
UserForm2.Show
End Sub
The line Rs.Fields.name is not recognised.
I can't use any other tool apart from Excel so I can't import CSV into anything else. (their rules not mine).
As you can tell i'm a newbie so any advice will be greatly appreciated.
