How do I Use a custom function to grab data from a CSV File

Question

This is my first attempt at custom functions in excel. I have been having troubles getting my csv file to import into an existing workbook so I thought I'd use a custom function to pull the data I need so i attempted to implement that function as a vba module:

Test file: Potter.csv (only has 2 records)

The function call I used is

=ImportCSVData("Potter.csv",D2,F1)

and

=ImportCSVData("Potter.csv","D2","F1")

Both were met with the #VALUE! error and I'm unsure how to resolve it

I created the following:

Function ImportCSVData(csvFilePath As String, sourceCell As String, destinationCell As String)
    Dim ws As Worksheet
    Dim csvWb As Workbook
    Dim csvWs As Worksheet
    
    ' Get the current worksheet
    Set ws = Application.Caller.Worksheet
    
    ' Open the CSV file
    Set csvWb = Workbooks.Open(csvFilePath)
    Set csvWs = csvWb.Sheets(1)
    
    ' Get data from the source cell in CSV and put it in the destination cell
    ws.Range(destinationCell).Value = csvWs.Range(sourceCell).Value
    
    ' Close the CSV file without saving changes
    csvWb.Close False
    
    ' Release memory
    Set csvWs = Nothing
    Set csvWb = Nothing
    Set ws = Nothing
End Function

Here is a screenshot of the test csv file:

I expected it to return "James Potter" in the cell I specified but instead both were met with the #VALUE! error and I'm unsure how to resolve it

`csvFilePath` probably requires the full path: ie. `"C:\Temp\Potter.csv"` — Scott Craner, Aug 18 '23 at 17:11
Your function should return the data, not (attempt to) write it to the cell. UDFs are meant to return a value and aren't meant to modify cells, though they can in some limited and edge cases. — BigBen, Aug 18 '23 at 17:13
https://www.ablebits.com/office-addins-blog/advantages-custom-functions-excel/#:~:text=A%20UDF%20cannot%20copy%20and,you%20should%20use%20conditional%20formatting. — Tim Williams, Aug 18 '23 at 17:25
This may help ... https://davetallett26.github.io/excel-markdown.html — user10186832, Aug 18 '23 at 18:04

Tim Williams · Answer 1 · 2023-08-20T19:14:28.750

This might be the only workaround for opening a file (in Excel) in a UDF:

Function GetCSVData(path, rng As String)
    Dim v, wb As Workbook, xl As Excel.Application
    Set xl = New Excel.Application
    Set wb = xl.Workbooks.Open(path, ReadOnly:=True)
    v = wb.Worksheets(1).range(rng).Value
    wb.Close False
    xl.Quit
    GetCSVData = v
End Function

Spinning up a new Excel instance is pretty slow though.

Storax · Accepted Answer · 2023-08-19T08:41:26.653

My assuption is you've got a comma separted text file and the lines are separated by VBCRLF.

This function reads the complete textfile into a string

Function getTextfile(ByVal fileName As String) As String
   
    Dim fileNr As Long
    fileNr = FreeFile
        
    Open fileName For Input As fileNr
    getTextfile = Input(LOF(fileNr), fileNr)
    Close fileNr

End Function

One can take the output of this function to split it and retrieve a certain value

 Function getCSVData(inpFile As String, col As String, rowNr As Long) As String

    Dim vdata As Variant
    vdata = getTextfile(inpFile)
    vdata = Split(vdata, vbCrLf)
    
    ' Be Careful here.
    ' we have 0-based arrays
    Dim inpRow As Variant
    inpRow = vdata(rowNr - 1)               'get the complete row
    
    Dim colNr As Long
    colNr = colLetter2Number(col) - 1 
    getCSVData = Split(inpRow, ",")(colNr)  'get the column 

End Function

The function colLetter2Number will just convert a letter to a column number

Function colLetter2Number(inpLetter As String) As Long
    ' This will work in this case because an active sheet is available
    ' Otherwise use a function which is not dependant on an active sheet
    ' https://stackoverflow.com/a/57774110/6600940
    colLetter2Number = Columns(inpLetter).Column
End Function

You can use getCSVData in a sheet as an UDF =getCSVData("D:\TMP\data.txt","D",4) which you should probably not do that often as it will open the text file several time when updating.

One can test it like that

Sub testIt()
    Dim fileName As String
    fileName = "D:\TMP\data.txt"
    Debug.Print getCSVData(fileName, "D", 4)
End Sub

Watch out for files where one or more values contain a comma and have been wrapped in double quotes - your parsing will break down in those cases. — Tim Williams, Aug 19 '23 at 21:08
Right, I know, my solution is not fool proof. But one does not need to use excel to open a CSV file for an UDF. And even with Excel you will run into pitfalls like a client phone 0049123456789. — Storax, Aug 20 '23 at 05:56
No in most cases this is fine - just a heads-up to the OP in case they had data like that…. — Tim Williams, Aug 20 '23 at 17:11

How do I Use a custom function to grab data from a CSV File

2 Answers2