Read CSV Using VBA techniques

by Selva V Pasupathy

The following code uses Active Database Objects (ADO) to read a comma-separated-values (CSV) text file.

On Error Resume Next

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001

Set objConnection = CreateObject(“ADODB.Connection”)
Set objRecordSet = CreateObject(“ADODB.Recordset”)

strPathtoTextFile = “C:\db\”

objConnection.Open “Provider=Microsoft.Jet.OLEDB.4.0;” & _
“Data Source=” & strPathtoTextFile & “;” & _
“Extended Properties=””text;HDR=YES;FMT=Delimited”””

objRecordset.Open “SELECT * FROM PhoneList.csv”, _
objConnection, adOpenStatic, adLockOptimistic, adCmdText

Do Until objRecordset.EOF
msgbox “Name: ” & objRecordset.Fields.Item(“Name”)
msgbox “Department: ” & _
objRecordset.Fields.Item(“Department”)
Wscript.Echo “Extension: ” & objRecordset.Fields.Item(“Extension”)
objRecordset.MoveNext
Loop

About these ads

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: