Set a reference to "Microsoft Scripting Runtime" in your project, then
insert the following code into a module:
== START OF CODE ===========================
Public Sub ImportDelimitedFileToRows(ByVal fname As String, _
Optional ByVal delim As String = ",")
'This code assumes that the file contains one line separated
'by a specific delimeter character. The code could be further
'refined to repeat the logic for multiple lines, but as it
'is written it will only read the first line of the file.
'Handle all errors.
On Error GoTo Err_ImportDelimitedFileToRows
'Declare procedural variables.
Dim fs As FileSystemObject
Dim ts As TextStream
Dim s As String
Dim lCPos As Long
Dim lPPos As Long
Dim sData As String
'Use scripting to access the text file.
Set fs = CreateObject("Scripting.FileSystemObject")
Set ts = fs.OpenTextFile(fname, ForReading, False, TristateFalse)
'Get the data.
s = ts.ReadLine
'Start at the beginning.
lPPos = 0
'Continue through the end of the data.
Do While lPPos < Len(s)
'Find the next delimiter.
lCPos = InStr(lPPos + 1, s, delim, vbBinaryCompare)
'If no more delimiters, get the remaining data.
If lCPos = 0 Then lCPos = Len(s) + 1
'Parse the data between the previous and current delimiter.
sData = Mid$(s, lPPos + 1, lCPos - lPPos - 1)
'Change this to do what you want with the "row" data.
Debug.Print sData
'Set the previous delimiter position to the one just used.
lPPos = lCPos
Loop
'Destroy the local objects.
ts.Close
Set ts = Nothing
Set fs = Nothing
Exit_ImportDelimitedFileToRows:
Exit Sub
Err_ImportDelimitedFileToRows:
'Better error handling can be implemented here.
MsgBox "An error occurred importing the file.", , "Message"
Resume Exit_ImportDelimitedFileToRows
End Sub
=== END OF CODE ==========================
You would call the routine using the following syntax:
ImportDelimitedFileToRows "file.txt", ","
The routine allows for other delimiters, as well.
The routine could easily be converted to a function that returns the number
of "records" retrieved by implementing a counter variable within the loop.
The particular way in which you save that data to your table will depend on
whether you choose to use DAO or ADO, so I have intentionally left out the
code for that portion. If you would like further help with this, please ask
and I will be happy to help.
My email address is spelled out below.
As an aside, I would like to add that by far the easiest way to accomplish
what I have just coded above is to open your text file in Microsoft Word,
and do a global search and replace to change "," to "^p". This will replace
all commas with paragraph characters, and you can then import your file as
you normally would.
Cheers!
[email protected]