Excel VBA Read Text File

So how can you import the contents of a csv or
text file into your Excel worksheet:

Find out:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
'========================================================

'DESIGNED & CODED BY LOEBLCOM SERVICES 2013

'ERIK LOEBL(713)409-7041

'EMAIL: erik@loeblcomservices.com

'WEB:   http://loeblcomservices.com

'========================================================





Sub query_text_file()

    

    'PURPOSE: Query A Text File

    

    '*************************************

    'clear 'Text File Contents' worksheet

    '*************************************

    Worksheets("Text File Contents").Select

    Range("A2:K6500").Clear

    

    '*************************************

    'now populate copy the orders over

    '*************************************

    

    Dim strPath As String

    

    'Need to reference the:

    '   Microsoft ActiveX Data Objects 2.5 Library

    Dim s_rst As ADODB.Recordset

    Dim s_cnn As ADODB.Connection 's for sub connection

    Dim intRow As Integer

    

    Const adOpenStatic = 3

    Const adLockOptimistic = 3

    Const adCmdText = &H1

   

    Set s_cnn = New ADODB.Connection



    strToolWkbk = "read_file.txt"

    strPath = ThisWorkbook.Path & "\read_file"



    Debug.Print strPath

    

    

    'For a text file, Data Source is the folder, not the file

    s_cnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath & ";" _

    & "Extended Properties=""text;HDR=Yes;FMT=Delimited"";"

    

    s_cnn.Open

    Set s_rst = New ADODB.Recordset

    



    strSQL = "SELECT * FROM " & strToolWkbk



        

    s_rst.Open strSQL, _

        s_cnn, adOpenStatic, adLockOptimistic, adCmdText



    intRow = 2

    

    s_rst.MoveFirst

    



    Do Until s_rst.EOF

        Range("A" & intRow) = s_rst(0)

        Range("B" & intRow) = s_rst(1)

        Range("C" & intRow) = s_rst(2)

        Range("D" & intRow) = s_rst(3)

        Range("E" & intRow) = s_rst(4)

        Range("F" & intRow) = s_rst(5)

        Range("G" & intRow) = s_rst(6)

        Range("H" & intRow) = s_rst(7)

        Range("I" & intRow) = s_rst(8)

        Range("J" & intRow) = s_rst(9)



        intRow = intRow + 1

        s_rst.MoveNext

    Loop

    

    Range("A:C").Select

    Selection.NumberFormat = "General"

    

    Range("D:D").Select

    Selection.NumberFormat = "m/d/yyyy"

    

    Range("E:K").Select

    Selection.NumberFormat = "General"

    

    Range("A1").Select

    MsgBox "DONE."

    

    s_rst.Close

    s_cnn.Close

    

    Set s_rst = Nothing

    Set s_cnn = Nothing

        



End Sub

Click here for the file:
VBA Read Excel File.zip

“Go Fund Me” Page

($5 suggested amount)

(…10% of your gift amount will go to charity)

by