Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function to Query CSV file
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function to Query CSV file
Check out the basic VLOOKUP() Function in Help -- It will do this for you.
"Jason" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function to Query CSV file
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 "JMay" wrote: Check out the basic VLOOKUP() Function in Help -- It will do this for you. "Jason" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function to Query CSV file
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function to Query CSV file
i think there is a confusion on terms. You can either read/query two different ways 1) If you open the CSV file it will become a worksheet. Yo can use a number of different methods to retrievve data from the CVS file. vlookup() is one of these methods. Yo can also use the find method from VBA to get data from the worksheet 2) If yo don't want to open the CSV file as a worksheet then you can use the ADO method like arjen code. Hope this clarifies the problem so a novice will understand the differences. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=192203 http://www.thecodecage.com/forumz |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SQL Query Function | Excel Programming | |||
Function query | Excel Programming | |||
query about VALUE function | Excel Worksheet Functions | |||
Microsoft Query rejects "nz" function in Access Query | Excel Discussion (Misc queries) | |||
mid function in ms query | Excel Discussion (Misc queries) |