Function to Query CSV file

[Jason]

I have a simple csv file with two fields (employee number, employee name).

I would like to build a custom function (something like GetEmployeeName), so
that when the user enters =GetEmployeeName('12345'), it will query the csv
file, look for employee number 12345 and return that employee's name.

I have very basic VBA and SQL skills. Basically enough to know this is
possible, but not enough to make it happen.

Has anyone written anything like this in the past?

Appreciate any help.

Thanks

Jason

 

[JMay]

Check out the basic VLOOKUP() Function in Help -- It will do this for you.

 

[Jason]

Thanks JMay

I am have simplified my example a little too much. There will be some
additional logic built into the SQL that would prevent using vlookup. Also
the csv file will eventually be loaded to an Access database, also preventing
the use of vlookup.

I am basically just trying to learn how to establish the connection to the
csv file and return a value based on parameters passed.

Appreciate your response.

Jason

 

[arjen van der wal]

Hi Jason,

This can be done using ADO and querying the csv file with an SQL statement
as if the csv file was a database table. It requires adding a reference to
ADO in your project. In Visual Basic Editor, go to Tools and then References
and then add a reference to the latest version of Microsoft Active-X Data
Objects Library.

Here's an example adapted from something I created awhile ago based on a
parameter which is entered on a userform with a textbox. There are other ways
to enter the parameter, using a range value or an Input Box, so you may want
to play around with the procedure a bit and fit it to your needs.

Sub QueryTextFile()

Dim EmpNumber As Long
EmpNumber = CLng(frmQuery.txtEmployeeNumber.Value)

Unload frmQuery

Const sConnect As String
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Queries\;" & _
"Extended Properties=Text;"

Dim sSql As String
sSql = "SELECT * " & _
"FROM MyFile.csv " & _
"WHERE EmployeeNumber =" & EmpNumber

Dim rsData As ADODB.Recordset
Set rsData = New ADODB.Recordset

rsData.Open sSql, sConnect, adOpenForwardOnly, adLockReadOnly, adCmdText


If Not rsData.EOF Then
Sheet2.Range("A2").CopyFromRecordset rsData
Sheet2.UsedRange.EntireColumn.AutoFit
Else
MsgBox "No Records Returned", vbCritical
End If

rsData.Close
Set rsData = Nothing

End Sub

This example assumes the use of Office 2003. If you're using 2007 then:

sConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:\Queries\;" & _
"Extended Properties=Text;"

Also note that in the connection string, you just need to put the folder
where the csv file is located. The actual filename goes in the SQL query.